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.

 

[kod2th3e]

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)

It indicates true. When you say the list must be serial dates do you mean that they have to be consecutive? or ?? For example, below is a partial list:

1/1/09
1/2/09
1/3/09
1/4/09
1/9/09
1/10/09
1/11/09
1/17/09

So there isn't really any perdictable pattern to the list and it doesn't contain just holidays because there are quite a bit of days that we get off that are not holidays. I figured that I could input all dates off in a list and label it holidays.

 

[Andrew Poulsom]

By serial dates I mean they must be a true date (number) not text that looks like a date. The order doesn't matter.

Please provide some sample data (including cell references) where the formula I posted isn't working.

 

[kod2th3e]

By serial dates I mean they must be a true date (number) not text that looks like a date. The order doesn't matter.

Please provide some sample data (including cell references) where the formula I posted isn't working.

Cell F6 is an input. I have it automatically entering the date/time in the format of: MM/DD/YYYY HH:MM AM/PM Cell G6 is the "due date" this is populated by the formula:

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

Cell L2 is named "Days" and has a value of 2 entered, Cells M2 - M12 are named "Holidays" and have the following dates:

9/4/09, 9/5/09, 9/6/09, 9/7/09, 9/12/09, 9/13/09, 9/18/09, 9/19/09, 9/20/09, 9/26/09, 9/27/09.

If a date/time of 9/3/2009 8:45 AM is entered into cell F6 then a date/time of 9/9/2009 8:45 AM should be displayed in cell G6 (because 9/4, 9/5, 9/6, and 9/7 are in the holiday list) It currently displays 9/5/2009 8:45 AM.

I hope this is what your looking for and helps.

Thanks,
-Cody

 

[Andrew Poulsom]

Sorry, I forgot that your start date included the time:

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

 

[kod2th3e]

Andrew,
No need to apologize. I plugged in the change and it appears to be working great. Thank you again for all your help.

Thanks,
-Cody