Skip to main content

Hi Community,


I have a cruddy report that cannot give me the data i need which forces me to calculate the number of days a person has been helping our organization and I was hoping there might be a formula that can help automate the calculation a bit, but I cannot wrap my head around it (terrible with IF formulas). The report is run once per month and will produce 4 different combinations of dates for the in and out field.




  • Combination 1, OUT date is prior to the start of the month, and IN date is after the last date of the reporting month OR blank . This calculation would then be the same as the number of days in the month. Example for May (OUT is 4/20/21 and IN is 6/5/21 or blank, would = 31 days)




  • Combination 2, OUT date is prior to the start of the month, and IN date is within the month of reporting. This calculation would be the datediff between the IN date and the last day of the previous month. Example would be (OUT 4/20/21, IN 5/14/21= 14 days)




  • Combination 3, OUT date begins within the reporting month and IN date is the next month OR blank. Calculation would be the datediff between the OUT date and the 1st of the next month. Example (OUT is 5/14/21 and IN is within the next month OR blank= 17 days)




  • Combination 4, OUT date begins within the reporting month and IN date is also within the reporting month. Calculation would be the date difference between both dates. (OUT is 5/1/21 and IN is 5/14/21 = 13 days)




Some date examples from my table below:


I appreciate any help (if possible) the community could provide.

To make things simpler, I would recommend having a formula field called {First of Month} like so:


DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'YYYY-MM-01'))

And another one for {Last of Month} like so:


DATEADD(DATEADD({Start of Month},1,'month'),-1,'day')

The above isn’t necessary to keep as separate fields, it just shortens the necessary formula for next field. Your date difference formula would be:


IF(

   AND({Foster Out} < {First of Month}, OR(NOT({Foster In}), {Foster In} >= {Last of Month})), 

   DAY({Last of Month}), 

IF(

   AND({Foster Out} < {First of Month}, {Foster In} < {Last of Month}), 

   DAY({Foster In}), 

IF(

   AND({Foster Out} >= {First of Month}, {Foster Out} <= {Last of Month}, OR(NOT({Foster In}), DATETIME_DIFF({Foster In}, {First of Month}, 'months') = 1)), 

   DATETIME_DIFF({Last of Month}, {Foster Out}, 'days') + 1, 

IF(

   AND({Foster Out} >= {First of Month}, {Foster Out} <= {Last of Month}, {Foster In} >= {First of Month}, {Foster In} <= {Last of Month}), 

   DATETIME_DIFF({Foster In}, {Foster Out}, 'days'

)))))

I used DAY() instead of DATETIME_DIFF() in a few instances since some of your described logic for the difference between dates would result in just the “day” number for a given date.

Thank you so much. Forgive the previous deleted comments. There was one = sign missing from a line, but it works perfectly now! This makes gathering the report so much simpler. Thank you.

Reply


Terms & Conditions