DATE FORMULA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RYAN - 24 Jun 2005 09:24 GMT

I am trying to add number of days, INCLUDING HOLIDAYS, to a date to get
a second date. However, the date formula in Excle always exclude
holidays. Is there any way I can make the calculate to include holidays
???

To make my self clear:

I want to forcast the arrival time of goods to factory's warehouse.
When I add the lead time to a certain date using the WORKDAY formula, I
get un-accurate date as the formula exclude weekend and holiday.
However, when shipping goods from vendor to the our factory we
caluculate HOLIDAYS (vessels at sea take no holidays).

How can use the WORKDAY formula calculate the future date INCLUDING
HOLIDAY ??? or is there any date frormula to do the job ????

Your help would be highly appreciated.

Thanks.

RYAN

Reply to this Message

JE McGimpsey - 24 Jun 2005 13:18 GMT

> To make my self clear:
>
[quoted text clipped - 6 lines]
> How can use the WORKDAY formula calculate the future date INCLUDING
> HOLIDAY ??? or is there any date frormula to do the job ????

I'm not sure I understand, exactly. The WORKDAY() function will not
exclude holidays unless you include the holidays as an argument, so if
your holidays are in a range named "holiday_range", this formula will
exclude holidays:

=WORKDAY(A1,A2,holiday_range)

while this formula will not exclude holidays:

=WORKDAY(A1, A2)

If you want to add just a number of days, and not exclude holidays OR
weekends, use

=A1 + A2

Reply to this Message

RYAN - 24 Jun 2005 16:20 GMT

1) Does it mean that I have to set a list of holidays to be used as
holidays argument ???
2) I have found the following formula, and I think it work
=Date(Year(ref)+years,Month(ref)+months,Day(ref)+days)

To add (or subtract) a certain number of years, months, and/or days to
a date in a Works spreadsheet.

Reply to this Message

JE McGimpsey - 24 Jun 2005 16:43 GMT

> 1) Does it mean that I have to set a list of holidays to be used as
> holidays argument ???
[quoted text clipped - 3 lines]
> To add (or subtract) a certain number of years, months, and/or days to
> a date in a Works spreadsheet.

Your formula (2) works in XL as well to add days, months and years
without skipping weekends or holidays. HOWEVER, note that adding months
is problematic, since months have different numbers of days. For
instance, adding 1 month to 31 January 2005 might logically mean 28
February 2005 to some (e.g., that's what VBA's DateAdd function
returns), but XL, using

A1: 1/31/2005
A2: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)

will return the "31st of February", or 3/3/05.

If you have just want to add a number of days, without skipping weekends
or holidays, you can use arithmetic:

=ref + days

If you want to skip weekends, but not holidays, use

=WEEKDAY(ref, days)

If you want to skip weekends and holidays, use

=WEEKDAY(ref, days, holidays)

where holidays is either an array:

=WEEKDAY(ref,days,{"1/1/2005","5/30/2005","7/4/2005","12/25/2005"})

or holidays is a range of cells with date entries.

Reply to this Message

RYAN - 24 Jun 2005 17:27 GMT

Thanks for your clarification. I will try to use WEEKDAY formula to
calculate production schedules, and WEEKDAY(ref,days.....) formula to
calculate delivery lead time. I will update my post with the result.

Thanks again.

Reply to this Message

Mac Forum / Applications / Excel / June 2005

DATE FORMULA