Confounded by date differences
On Mar 3, 1:15*pm, DubboPete wrote:
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
Hi Fred,
Impossible without sending you the original data/spreadsheet. I have
however managed to pin down the mainframe guys to export it in Excel
format, instead of raw csv, and it seems to have fixed 80% of the
errors. So I will cope with a little manual manipulation on the
negative values each month.
But thanks for your help
Pete
|