Rolling Year function

[canadianpassport]

Hi,

I'm creating a spreadsheet that calculates the end date of an employees training period. I've used the WORKDAY function to calculate the end date based on the Start Date and the number of days they are in training. the issue is the holiday compnent of the WORKDAY function only appears to remove one day for each date entered rather than a date range. E.g They are on holiday between 05/08/2014 - 22/08/2014 the function would only add two days to the end date rather than the total number of work days missed. Is there any way a date range can be used with the WORKDAY function, or is there a better way for me to acheive what I want?

Many Thanks

[Ron Coderre]

Excel doesn't assume that, if you list 2 dates, you want a range of dates. Typically, users do not want date ranges assumed....New Year's Day, etc.
You need to list every holiday date.

[canadianpassport]

Thank you for your reply, I understand what you say above. My question is can date ranges be used with the WORKDAY function or do I need to approach the problem from a different angle.

[rajansettu]

hi

will you attach a sample file..

rajan

[canadianpassport]

Book2.xlsxHi,

Please attached the workbook. The two dates I have entered only add two days to the end date, I would like them to be counted as a range.

Many Thanks

[kvsrinivasamurthy]

Try this in K2

Please Login or Register  to view this content.

If both days are inclusive then

Please Login or Register  to view this content.

[canadianpassport]

Excellent!!! Thank you very much

[Ron Coderre]

I don't think that quite works.
Example:
Start Date: 8/1/2014
Days to complete: 30
Absence time: 8/5/2014 thru 8/22/2014

This formula:

Please Login or Register  to view this content.

returns: 10/8/2014

However, this formula:

Please Login or Register  to view this content.

returns: 10/1/2014
Which I believe is correct

Details:

Please Login or Register  to view this content.

[canadianpassport]

Hi Ron,

Indeed your example does provide the required result. Many thanks for the reply.

[Ron Coderre]

Glad you got something you can use.