Due Date & Time

[kod2th3e]

I want to be able to calculate a due date two days from an entered date/time. If I input 9/3/2009 3:00 PM into cell F6 I would like cell G6 to display 9/5/2009 3:00 PM. The formula that I am currently using includes a holiday list so if the due date lands on a day within that list it gets pushed out.

My formula is =if(F6="","",Workday(F6,2,Z1:Z144)).

Basically when F6 gets populated G6 is supposed to populate with a due date of two days later (same time of the day). Currently if my input date/time is 9/3/2009 3:00 PM the formula is displaying a due date of 9/5/2009 12:00 AM. Any ideas or help would be great. Thanks for taking the time to read.

 

[Andrew Poulsom]

Try eg:

=IF(F6="","",WORKDAY(F6,2,Z1:Z144))+F6-INT(F6)

 

[kod2th3e]

Is it possible to do this without omitting Saturdays or Sundays. So, have a due date land on Saturday or a Sunday if it is 2 days after the start date. Is there another function other than the workday function that can allow for this?

 

[Andrew Poulsom]

Wouldn't this do it?

=IF(F6="","",F6+2) <!-- / message --><!-- sig -->

 

[kod2th3e]

That is what I orginally used but, I would like to include a holiday list so that if the due date lands on Easter or Christmas (or whatever is in the list), it would change to the next available working day. Is this possible, thoughts?

 

[Andrew Poulsom]

Put the number of days to add in a cell and name it Days. Name your list of holidays Holidays. Then try:

=IF(F6="","",F6+SMALL(IF(ISNA(MATCH(F6+SIGN(Days)*(ROW(INDIRECT("1:"&Days*10))),Holidays,0)),ROW(INDIRECT("1:"&Days*10))),Days))

which must be confirmed with Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround the formula with curly braces {}. If incorrectly entered the formula will return #NUM!.

 

[kod2th3e]

Andrew,
First off, thank you very much for your time and help in this matter; I appreciate it very much. Secondly, I'm not sure what I am doing wrong but I am not getting a date/time when I enter the formula. I am getting #VALUE!. In L1 I typed Days and put the number 2 in L2 in M1 I typed Holidays and from M2 down I put some dates that I want to have excluded. When I entered the formula I pressed ctrl+shift+enter to get the curly brackets {} and still nothing. I am not sure what I am doing wrong, do you see or know?

Thanks,
-Cody

 

[Andrew Poulsom]

You need to name the ranges Days and Holidays using Insert|Name|Define.

 

[kod2th3e]

I've got it showing a date/time now, but it's not omitting anything on the holiday list does the holiday list have to be formatted a certain way? I have mm/dd/yyyy.

 

[Andrew Poulsom]

I've got it showing a date/time now, but it's not omitting anything on the holiday list does the holiday list have to be formatted a certain way? I have mm/dd/yyyy.

No, but the list must be serial dates. The ISNUMBER function will return TRUE if its argument is a date. What does this return?

=ISNUMBER(M2)