• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updated file - Need help when LOA is entire month off

L

Leigh Digons

Member
I am okay with these formulas although they are a bit complex. However the formula fails when the LOA is for an entire month. It returns 30 for Days Out of Office, instead of 31 days. How do I fix this?
 

Attachments

When you do calculation, don't subtract from EOMONTH(T$6), but from EOMONTH(T$6)+1.

This is because your mathematical operation is as following:
1/31/2015 - 1/1/2015 = 30

With correction
2/1/2015 - 1/1/2015 = 31

Same goes for most other date calculation. You have to decide if End date is inclusive or exclusive.
 
It should be exclusive because they start back on the date their LOA ends, so the end date should be INCLUDED in days in the office, but in the case where the LOA goes over into the next month, the last day of the month needs to be counted.
 
Let me explain a bit further.
Excel stores date & time as numeric value.
Ex. 1/31/2015 = 42035.00

Time is stored as fractional value (1 hour = 1/24).

Where .00 means 12:00 AM.

Therefore subtracting 1/1/2015 12:00 AM from 1/31/2015 12:00 AM will exclude 1/31/2015.

That's why you need to add +1 to include end of month.

Edit: Additional bit.
So if Start Date is when Employments starts and End Date is the last date in office.
"End date" + 1 - "Start Date" = Days in Office

If Start Date LOA is first day out of office and End Date is first day back in office.
"End date" - "Start Date" = Days out of Office

If Start Date LOA is first day out of office and End Date is last day of LOA.
"End date" +1 - "Start Date" = Days out of Office
 
Last edited:
No no. You are specifically calculating End of Month in your formula as EOMONTH(T$6). So you are adding +1 to end of month part of calculation.
 
Here's your formula modified. Only Changed portion

=IF(H8="",0,(IF(OR($C8>T$6,$C8=""),(((MAX(0,MIN(EOMONTH(T$6,0)+1,$I8)-MAX(T$6,$H8))))))))+IF(J8="",0,(IF(OR($C8>T$6,$C8=""),(((MAX(0,MIN(EOMONTH(T$6,0)+1,$K8)-MAX(T$6,$J8))))))))+ IF($L8="",0,(IF(OR($C8>U$6,$C8=""),(((MAX(0,MIN(EOMONTH(T$6,0)+1,$M8)-MAX(T$6,$L8))))))))
 
So right now my formula is: =IF(Q8="",0,(IF(OR($L8>AC$6,$L8=""),(((MAX(0,MIN(EOMONTH(AC$6,0),$R8)-MAX(AC$6,$Q8))))))))+IF(S8="",0,(IF(OR($L8>AC$6,$L8=""),(((MAX(0,MIN(EOMONTH(AC$6,0),$T8)-MAX(AC$6,$S8))))))))+ IF($U8="",0,(IF(OR($L8>AD$6,$L8=""),(((MAX(0,MIN(EOMONTH(AC$6,0),$V8)-MAX(AC$6,$U8))))))))


How do I modify it so that I am excluding the last day when it's within the month and including the last day when it goes over a month? I also think I have too many $'s in there. I want to copy this formula over to the Feb, March, April sections as well and I think having the $'s will cause some problems. Sorry for all the newbie q's!
 
You must log in or register to reply here.
Back
Top