Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Confounded by date differences
Hi all,
I have 24,000+ records in my spreadsheet, all originating from the same database. It would seem that this database has a mind of its' own when it comes to producing date and time fields. Background, I am trying to calculate the working days and hours between two date cells. The formula I am using was provided by good people on this group. It is: =((NETWORKDAYS(H2,I2)-1)*("17:00"-"08:00")+MOD(I2,1)-MOD(H2,1))*24 (answer 1.5 where H2 = 13-09-2009 2:00 PM and I2 = 13-09-2009 3:30 PM ) However, and no matter how I try and work this out, there are results in other fields that show as negative! Now the times are based upon road trips; booking out the vehicle, then booking it back in! The database won't let you book a vehicle back in before it left, obviously. So, my problem is, why do I get negative values for some calcs, and not others? I hope it's a formatting thing, but I cannot work out how to get the consistency across all the cells Any help would be appreciated! Pete |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting data by date differences | Excel Worksheet Functions | |||
Date and time differences revisited | Excel Discussion (Misc queries) | |||
Calculating Date AND Time Differences | Excel Worksheet Functions | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
calculate date differences in years and months | Excel Worksheet Functions |