Annual Leave Form - Sum the workdays (value of 1) if they fall between Start Day & End Day

Hi Southward,

I just attempted this and it's not generating the correct results. Take the example I uploaded. The start date being 10 September 2016 AM and the end date being 24 September 2016 AM should generate a result of 11.5 work days. Your formula is giving me a result of 6.5.

It looks like the reason for this is because your formula instructs the cell to only count either AMs or PMs (depending which AM or PM criteria is showing the start dates/end dates). What I need the formula to do is identify whether the Start date begins at AM or PM and begin counting the '0.5' values from the relevant row, as well as detect whether the End date ends on AM or PM and end counting the '0.5' values on the relevant row, but count ALL AM and PM vales in between these rows.

Hi Stuartxl,

I see what you mean. I was wondering the same thing as I formulated the first idea.

In order to bring the AM/PM into play for excel to calculate between dates taking the AM/PM into consideration I added a helper column. Column BC.
It looks at Column BB to determine AM/PM and returns the Column AX date into an AM/PM relative value. (wow, very wordy, hope it makes sense.

The new formula in M16 turns the M12 and M14 into AM/PM relevant date/time values as directed by U12 and U14. Which is strange because U12 and U14 can only be AM values (for now).

See attached file.

Hope this works for you.

Cheers



P.S. or what John Topley suggested. That sure is pretty.

Hi John and Southward,

Both your solutions work perfectly. I only saw your replies after my last post, but thanks for assisting. I think I'll use the solutions provided as my solution involved generating more data, whereas yours work with only the information supplied on my first upload.

Thanks again.

PS John, I'm beginning to think you're my guardian angel on this forum - every thread I post has a solution from you! Thank you very much. Unfortunately this forum won't let me add to your reputation anymore, which doesn't seem fair since you seem to be the one helping me the most.