+ Start a Discussion
mjfroehlichmjfroehlich 

Calculating Days Served per Year

Please help! I'm new at this. I'm trying to calculate the number of days served in the year. My data fields are date/time but I'm only wanting to calculate the days. I have some that came in the previous year (2011) but I only want to count the days in 2012. I have others that came in 2012 and are still there so I only want to count them through 12/31/2012.There is a total of 4 possibilities.  Here is the formula that I have created but I keep getting Syntax errors. I'm trying to say if the Date Time In and Out fit the criteria then calculate it this way.

 

IF (DAY_ONLY() Date_Time_In__c < 2012-01-01 and Date_Time_Out__c <2012-12-31,  Date_Time_Out__c  -  2012-01-01  + 1,
(DAY_ONLY() Date_Time_In__c > 2012-01-01 and Date_Time_Out__c <2012-12-31, Date_Time_Out__c   - Date_Time_In__c  + 1,
(DAY_ONLY() Date_Time_In__c < 2012-01-01 and Date_Time_Out__c ISBLANK, 2012-12-31  2 - 012-01-01 + 1,
(DAY_ONLY() Date_Time_In__c > 2012-01-01 and Date_Time_Out__c ISBLANK, 2012-12-31  - Date_Time_In__c
))))

 

Is there an easier way to do this?

 

Thanks so much in advance for your help.

Best Answer chosen by Admin (Salesforce Developers) 
MellowRenMellowRen

mjfroehlich

 

Wow. I deliberately wanted to avoid using a host of nested IF statements but thought never-the-less that this would be quite an easy formula to write. Instead it has captivated me as a puzzle for more than an hour—Salesforce’s treatment of DATETIME fields in formulas is quite restricting. This does what you need:

 

MAX(MIN(NULLVALUE(Date_Time_OUT__c, NOW()) - NOW(), DATE(2012,12,31) - TODAY()) - MAX(Date_Time_IN__c - NOW(), DATE(2012,1,1) - TODAY()) + 1,0)

 

By the way, DAY_ONLY() is an APEX function. You can use it when writing classes/triggers/etc but not in formulas.

 

Good luck.

MellowRen

All Answers

MellowRenMellowRen

mjfroehlich

 

Wow. I deliberately wanted to avoid using a host of nested IF statements but thought never-the-less that this would be quite an easy formula to write. Instead it has captivated me as a puzzle for more than an hour—Salesforce’s treatment of DATETIME fields in formulas is quite restricting. This does what you need:

 

MAX(MIN(NULLVALUE(Date_Time_OUT__c, NOW()) - NOW(), DATE(2012,12,31) - TODAY()) - MAX(Date_Time_IN__c - NOW(), DATE(2012,1,1) - TODAY()) + 1,0)

 

By the way, DAY_ONLY() is an APEX function. You can use it when writing classes/triggers/etc but not in formulas.

 

Good luck.

MellowRen

This was selected as the best answer
mjfroehlichmjfroehlich

MellowRen

 

Thank you so much for the time you spent on this. At first I did not think it was working but I changed the date to 2013 and it calculates perfectly for 2013. I am amazed! I still need to calculate it for the prior year (2012). I'm working on that, if you have any suggestions I would greatly appreciate it.

 

Thanks so much for what you have done.

Martha

MellowRenMellowRen

Martha

 

Sorry I am confused. The formula I gave works perfectly for how I interpreted your question (which basically was “How many days did an employee work in 2012?”).

 

Hence by my understanding of the requirement:

 

Date Time IN    Date Time OUT   Actual  Formula Comment
                                Delta   Result
1/3/2012        20/3/2012       20      20      Both dates in 2012
1/3/2011        20/3/2011       20      0       Both dates in 2011
1/3/2013        20/3/2013       20      0       Both dates in 2013
26/12/2011      14/1/2012       20      14      Start date in 2011
26/12/2012      14/1/2013       20      6       End date in 2013
26/12/2012                      ???     6       End date is blank

 

What results are you expecting? Let me know and I'll help make it work.

 

Regards

MellowRen

mjfroehlichmjfroehlich

MellowRen

 

I am so sorry. Your formula works GREAT!!! for both years! We have some problems on our end with data entry.

 

Thank you, Thank you, Thank you. We greatly appreciate your help with this.

Martha

 

MellowRenMellowRen

Martha

 

Bad data, always a bane. 

 

I am guessing you could set up some Validation rules to help prevent obvious mistakes in the future. Another idea is that you could wrap my formula in an IF statement with a condition looking for obvious errors (ie Date_Time_IN > Date_Time_OUT || ISBLANK(Date_Time_IN) etc) which if it is true you assign a negative number. Then by running a report with a filter on Days Served for a value less than 0 you could quickly find all the obvious mistakes.

 

Depends on how big your data set is and how often you are finding errors, if this would be worthwhile.

 

Anyway good luck. Glad I could help.

 

PS: I would love a Kudos :-)

 

Regards

MellowRen