Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Dates and/or Times
Good afternoon,
I'm dealing with a modified =NETWORKDAYS formula but cannot seem to get it to total my results (especially when there is no start date/time and stop date/time entered into some of the cells in the columns I'm totaling). Is there a way to get the empty cells to be ignored and give me a correct total (SUM), see example below: Cell D13 = 10/13/2008 9:00:00 PM Cell F13 = 10/13/2008 9:30:00 PM =(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1) Cell N13 = 00:30 =SUM(N13:OFFSET(N21,-1,0)) Cell D14 = <blank Cell F14 = <blank =(NETWORKDAYS(D14,F14)-1)*("15:00"-"06:30")+MOD(F14,1)-MOD(D14,1) Cell N14 = ############## (indicating negative dates or times) I'm trying to total a list of these results from cell N13 through cell N21 by using the following formula: =SUM(N13:OFFSET(N21,-1,0)) When I SUM all the cells (N13 through N20) I get 14:30 but that's incorrect, it should equal 48:30. Cell formats are [h]:mm and my cell values are as follows: N13 = 00:30 N14 = 02:15 N15 = 02:00 N16 = ###### N17 = ###### N18 = ###### N19 = 43:45 N20 = ###### If anyone can assist please reply, Mike |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
negative dates or times are displayed as # # # # | Excel Worksheet Functions | |||
negative dates or times are displayed as # # # # | New Users to Excel | |||
Negative times | Excel Discussion (Misc queries) | |||
negative times | Excel Worksheet Functions | |||
Negative times | Excel Discussion (Misc queries) |