excel date/time calculation 1

[Spott]

Hey gang - this could be done in excel or an access query, whatever you think is easier. I've been looking at too many numbers and am now clinically brain dead.

In my db I have 4 fields Start time, start date, end time, end date. I need to get the difference of time between end and start. If the events are on the same day I've gotten it to show the correct amount of time (hrs/min) but if it is greater than 1 day, it's still just the hrs/minutes.

I have about 300,000 rows of data to check and am hoping to find where there's a negative time to identify the error. (Where something ended before it was finished and our date/time stamps are off.)

Thanks!!!!!

 

[Griffyn]

Usually the following rules apply:

1. A Date is simply the number of days since a particular date, something like 1-Jan-1970.

2. Where 1 represents a whole day, the time is a proportion of this ie. midday is 0.5, 6pm is 0.75

So, presuming that your data is stored according to these rules, it's fairly easy to see if you have an end date that's before a start date. IF(StartDate+StartTime > EndDate+EndTime, "Culprit", "Normal&quot

Hope that helps.

 

[Spott]

My goodness...if it would have been a snake!! Already found one error, off to copy the formula a lot!!