missing deadline

[Guest]

Hi,
I would greatly appreciate if someone could help with the following: I am
trying to get a negative result for a missed deadline.

I have determined the formula when the deadline is met:
deadline (A1) report released (B1) result
01/11/05 6:00 pm 01/11/05 5:45 pm 0:15:00

I used the following formula to generate the above result:
=Mod(A1,1)-MOD(B1,1)+(NETWORKDAYS(B1,A1,Holidays)-1)*3/8

What forumula should be used if the report is released after the deadline;
based on the following parameters: monday-friday 9am-6pm workday; where
holidays are excluded and the deadline time is always 6:00 pm on the deadline
date?

What I'm getting now for a missed deadline is: ############

Thanks for your help!!

 

[Myrna Larson]

That's probably because the date and time is negative and Excel's 1900 date
system does not allow negatives dates or times.

The work-around is to change the workbook to the 1904 date system
(Tools/Options/Calculation). But if you do that, you'll find that all dates
are too high by 1462 days (4 years plus a day). To correct that, you must type
the number 1462 in a cell, Edit/Copy that cell, then select all of the
existing cells that contain dates, and Edit/Paste Special and select the
VALUES and SUBTRACT options.

 

[RagDyer]

How about simply changing the format of the cell containing the formula to
"General"?

Works for me.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi,
I would greatly appreciate if someone could help with the following: I am
trying to get a negative result for a missed deadline.

I have determined the formula when the deadline is met:
deadline (A1) report released (B1) result
01/11/05 6:00 pm 01/11/05 5:45 pm 0:15:00

I used the following formula to generate the above result:
=Mod(A1,1)-MOD(B1,1)+(NETWORKDAYS(B1,A1,Holidays)-1)*3/8

What forumula should be used if the report is released after the deadline;
based on the following parameters: monday-friday 9am-6pm workday; where
holidays are excluded and the deadline time is always 6:00 pm on the
deadline
date?

What I'm getting now for a missed deadline is: ############

Thanks for your help!!

 

[Guest]

Myrna,

Thank you so much re the 1904 work-around. That was so helpful : )
I'm almost where I need to be. Can you tell me how do I get the weekends to
be disregarded? "Holidays" and "Networkdays" are included in the formula, but
if the deadline is missed, the days are still being factored in. For example:

(Where 05/30/05 is a holiday and 05/28 & 05/29 are weekend days)

Deadline Released Result
05/27/05 6:00 pm 05/31/05 6:00 -24:00:00 (should be -8:00:00)
05/31/05 6:00 pm 05/27/05 6:00 8:00:00 (correct)

Thanks again for your kind assistance!