Time Calculations

[Stitcho]

Im not sure if this is best place to put this but...

I have a table where date and time is stored in one field. It is stored in the following format:

dd/mm/yyyy dd:hh:mm:ss

The way the table works is that the date & time gets associated to a status code in the system. So for example a case (lets say ID is 12345) in the system may have a status assigned of ENQ with a date of 01/01/2008 00:00:00:00 and then another record for the same case (12345) where the status is WIP with a date of 15/02/2008 00:00:00:00.

What I need to do is do is a time taken calculation between the 2 dates and times but I am not sure of the best way to do it, if at all.

Any help would be greatly appreciated.

 

[namliam]

Just substract the two dates
DateFinish - DateStart

Thats it...

 

[Stitcho]

Just substract the two dates
DateFinish - DateStart

Thats it...

Ah right, didnt think it would be that simple lol I'll give it a try

 

[gemma-the-husky]

this will return a double value which represents the number of days, with fractional part representing time

so
15/02/2008 00:00:00:00.
1/01/2008 00:00:00:00.

should return 45?

if you have times then

15/02/2008 12:00:00:00.
1/01/2008 00:00:00:00.

will return 45.5

 

[Stitcho]

Thanks for all the help. I have been trying it out in Excel first and it seems to work fine.

Now that is all fine for one of the reports I have, the other issue I now have is for a different report.

Basically the calculation is going to be the same be it needs to be broken down in more detail. So for example the time taken works out as 45.5 days. The 45.5 days needs to be broken down into days, hours, minutes and seconds if possible but they need to be split into seperate columns.

 

[Brianwarnock]

Int(fldname) will give the number of days
fldname-int(fldname) will give the time . you can use Format round it to format it as a string or do it in a time formatted field.

Brian