missing deadline

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!!

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.


On Tue, 25 Jan 2005 15:05:03 -0800, "MJOHNSON"
<[email protected]> wrote:

>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!!

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!
==============================================

"MJOHNSON" <[email protected]> wrote in message
news:[email protected]...
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,

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!


"Myrna Larson" wrote:

> 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.
>
>
> On Tue, 25 Jan 2005 15:05:03 -0800, "MJOHNSON"
> <[email protected]> wrote:
>
> >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!!
>
>