Determining holidays in a rolling 365 day period

angelode · May 20th, 2002, 05:17 PM

This concerns determining the amount of holiday an employee takes in a previous 'rolling' 12 month period. The employee is limited to just 25 days in each period.
There are four columns, the first contains the employees names, the second - the start of their vacation period, third the end of their holiday and fourth the number of days taken in that period (col 2 - col3).
My problems arise when the holiday starts before the relevant date or even when both the start and end fall before the relevant date in which case the latter results in a negative number.
This is what I did thus far:
I arrived at the 'relevant date' by using =Today() - 365 and column 5 has the following formula -
=IF((Holiday_start
I hope I have made myself clear but any help is appreciated.

angelode · May 20th, 2002, 05:20 PM

oops, a good start, part of the formula didn't appear!
It was:

=IF((Holiday_start
Thanks again

Mark W. · May 20th, 2002, 05:22 PM

angelode, Disable HTML.

angelode · May 21st, 2002, 12:12 AM

Thanks Mark
Not very clear my ramblings!
If this keeps on I'll end up with that flamey thinghy on this thread!
Here's the formula that I used:

=if((Start_date
[ This Message was edited by: angelode on 2002-05-20 23:18 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 20th, 2002, 05:17 PM	#1
angelode New Member Join Date: May 2002 Posts: 3	This concerns determining the amount of holiday an employee takes in a previous 'rolling' 12 month period. The employee is limited to just 25 days in each period. There are four columns, the first contains the employees names, the second - the start of their vacation period, third the end of their holiday and fourth the number of days taken in that period (col 2 - col3). My problems arise when the holiday starts before the relevant date or even when both the start and end fall before the relevant date in which case the latter results in a negative number. This is what I did thus far: I arrived at the 'relevant date' by using =Today() - 365 and column 5 has the following formula - =IF((Holiday_start I hope I have made myself clear but any help is appreciated.

May 20th, 2002, 05:20 PM	#2
angelode New Member Join Date: May 2002 Posts: 3	oops, a good start, part of the formula didn't appear! It was: =IF((Holiday_start Thanks again

May 20th, 2002, 05:22 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	angelode, Disable HTML.

May 21st, 2002, 12:12 AM	#4
angelode New Member Join Date: May 2002 Posts: 3	Thanks Mark Not very clear my ramblings! If this keeps on I'll end up with that flamey thinghy on this thread! Here's the formula that I used: =if((Start_date [ This Message was edited by: angelode on 2002-05-20 23:18 ]