Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
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" 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!! |
#3
|
|||
|
|||
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" wrote in message ... 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!! |
#4
|
|||
|
|||
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" 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel color palette missing colors | Excel Discussion (Misc queries) | |||
Missing values in Excel Line Chart | Charts and Charting in Excel | |||
PivotTable toolbar missing? | Setting up and Configuration of Excel | |||
how can i get the slope function to ignore missing data? | Excel Discussion (Misc queries) | |||
Identify missing criteria | Excel Worksheet Functions |