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

Confounded by date differences
 

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

Regards,
Fred

"DubboPete" wrote in message
...
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

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

Confounded by date differences
 

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

"DubboPete" wrote in message
...
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