workday function

[VegasNath]

I'm not sure that I am understanding properly how to use this function.

If today = 31/03/10, how do I get to the date of the previous / next workday etc?

For example, How on Tue 06/04/10 can I get to the previous workday, being Thursday 01/04/10.

[Rudi]

The workday function will provide a day that does not fall on a weekend (Sat or Sun), or on a public holiday that you add as a range into the optional third argument. The second argument allows one to specify the amount of days (positive for future date and negative for past date) to create the new date.

EG: =WORKDAY(TODAY(),5) will equal a date 5 days into the future. If it falls on a weekend it selects the next workday after the weekend.
Same in reverse if you specify -5 days.

[HansV]

Excel doesn't have built-in knowledge of holidays. You'll have to create a range of cells that list holidays that fall on Monday through Friday.
Let's say that you create a worksheet named Holidays, and enter holidays in (for example) A1:A50, including 02/04/2010 (Good Friday) and 05/04/2010 (Easter Monday).
On the worksheet where you want to use the WORKDAY function, enter a date in a cell, let's say you enter 06/04/2010 (dd/mm/yyyy format) in cell D1.
In another cell, enter the formula

=WORKDAY(D1,-1,Holidays!A1:A50)

This will return 02/04/2010.

You can streamline the formula by assigning a name to the range of holidays.

[VegasNath]

This will be very useful.

[Rudi]

There is another function called =NETWORKDAYS(StartDate,EndDate,[Holidays]), that woks out the amount of days between two dates (excluding the weekends and optional holidays). It is similar to WORKDAY, but provides a value representing the amount of days between, not a new date. It can also be of value if one works with WORKDAY.

PS: Do you know the "secret" function called DATEDIF? (Now there is a good function to know...It still amuses me why it is so "secret" in Excel)

[Goshute]

PS: Do you know the "secret" function called DATEDIF? (Now there is a good function to know...It still amuses me why it is so "secret" in Excel)

=YEARFRAC() IS documented, but not as useful. (I use it for some compounding functions where the return fits my needs.)

[VegasNath]

NETWORKDAYS, great, Thanks, I'll keep that one in mind! DATEDIF?, tell me more

[HansV]

DATEDIF was documented in the help files for Excel 2000, but for reasons unknown outside Redmond, not in any other version up to and including Excel 2007 (I don't know about Excel 2010).

Chip Pearson has kindly written up how it works: DATEDIF Worksheet Function.

[VegasNath]

Thanks, also useful.

[Rudi]

Chip Pearson has kindly written up how it works: DATEDIF Worksheet Function.

Thanks for that link. I like the tip about suppressing the 0 value output! It seems to clean up the formulas result quite nicely.