Time minus Time not working

[thedeadzeds]

Hi All,

I have a from which uses time in some of the formats. I want to be able to minus time from time. The following calculation does not currently add up and I cannot figure out why?

Available hours = Base hours minus unavailable hours.

Available hours should surely be a negative value if you use record 1 (07:30 – 09:22) but its currently showing 01:52. This make no sense.

Database attached. Please see Frm_Input

Many thanks
Craig

 

[Minty]

Time is stored as a fraction of a decimal part of a date time value.

So a a date time field is really a number like 12345.34567 where 12345 is the date and .34567 is the time.
You are effectively subtracting .xxx from .yyyy which is giving you weird results.
(If you format your result control to general number you will see you get -0.078125 !)

You will need to probably convert everything to minute values or make sure that your formatting and calculations are based around 1/1/1900 dates which is how a time field is actually stored e.g 1/1/1900 13:45.002345

 

[Frothingslosh]

You should look into the DateDiff function. I would advise calculating the difference in minutes, and then converting those to the HH:MM format.