Total calculated time is incorrect

C

CMTH

Total calculated time from 11:30pm to 12:10am is being returned as 23:20
instead of 00:40.
Expression being used is =Format([End time]-[Start Time],"Short Time")
 
C

CMTH

This makes sense.
I am using a date, but it is in a seperate column than the start and end
time. Is there a way to pull the date and include it in calculation?

Rick Brandt said:
CMTH said:
Total calculated time from 11:30pm to 12:10am is being returned as 23:20
instead of 00:40.
Expression being used is =Format([End time]-[Start Time],"Short Time")

Database DateTimes always include both a date and a time. When you don't; see a
date it is December 30, 1899. When you cross midnight you are on the next day
December 31, 1899.

So...in your case if neither value is displaying a Date then both occur on
December 30, 1899 and the 12:10 AM occurs BEFORE the 11:30 PM, not after.
 
C

CMTH

This makes sense.
I have a date, but it is in its own column as the start and end time are.
Is there a way to pull the date and include it in the calculation?

Rick Brandt said:
CMTH said:
Total calculated time from 11:30pm to 12:10am is being returned as 23:20
instead of 00:40.
Expression being used is =Format([End time]-[Start Time],"Short Time")

Database DateTimes always include both a date and a time. When you don't; see a
date it is December 30, 1899. When you cross midnight you are on the next day
December 31, 1899.

So...in your case if neither value is displaying a Date then both occur on
December 30, 1899 and the 12:10 AM occurs BEFORE the 11:30 PM, not after.
 
C

CMTH

The time will never span more than 24 hours.
What are you referring to by adding a day to the end time?
 
R

Rick Brandt

CMTH said:
The time will never span more than 24 hours.
What are you referring to by adding a day to the end time?

If you test and see that the end time is before the start time then add a day to
it before you do the DateDiff().

DateDiff(IIf(EndTime<StartTime,DateAdd("d",1,EndTime), EndTime)=StartTime
 
C

CMTH

That worked-thank you!

Rick Brandt said:
If you test and see that the end time is before the start time then add a day to
it before you do the DateDiff().

DateDiff(IIf(EndTime<StartTime,DateAdd("d",1,EndTime), EndTime)=StartTime
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top