Confounded by date differences

[DubboPete]

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

 

[Fred Smith]

There's no way to know without seeing the data. Give us examples of the data
which is providing negative results.

Regards,
Fred

 

[DubboPete]

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

 

[Fred Smith]

You've lost me.

Your sample formula showed it calculating the difference between cells in
columns H and I. You then stated your problem was that sometimes the results
were negative.

Surely you can post some examples of where this happens. Just pick a few
rows where you are having the problem, and show us what's in H and I.

Regards,
Fred

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