Subtracting two date/time fields

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
647
Office Version
  1. 2016
Platform
  1. Windows
Hi
working in access 2016 In my table I have a Date/Time field formatted as m/d/yyyy h:mm
In a query I created 2new field called Time In and Time Out here is how I did this Time In: IIf([IN]="IN",[Message Date/Time],"") and Time Out: IIf([OUT]="OUT",[Message Date/Time],"")
I then wanted to subtract the Time In from the Time Out I keep getting errors which I believe is because my 2 fields are now text. How can I keep them as a Date/Time field

Thanks
L
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
If IN isn't IN, then you're making the value an empty string (zls). Same for Out.
zls can't be used in a mathematical expression so it doesn't matter if you try to subtract from it, or subtract it from something else. You'll need another approach; perhaps a numeric or date value for the False part of IIF. If you can't figure it out, sample data might help to figure out something.
 
Upvote 0
Hi
I still cannot get it to subtract my 2 date/time fields (Subtracting [FirstOfTime In] from [LastOfTime Out] This is what I have been trying; If either of the rows are null the first part of my formula works but where they both have dates I get #ERROR

TS: IIf(IsNull([LastOfTime Out]),"",IIf(IsNull([FirstOfTime In]),'",[LastOfTime OUT]-[FirstOfTime In]))
and this
TS: IIf(IsNull([LastOfTime Out]),"",IIf(IsNull([FirstOfTime In]),"",Nz([LastOfTime OUT]-[FirstOfTime In],0)))

Both Date\Time fields look like this
13/01/2020 8:53:38 AM Some are null


Thanks for you help
L
 
Upvote 0
I think it is a mistake to have a formula that returns a string value ("") or a date value. That's two different datatypes mixed together in one column, which is not good for database work.

It would be enough to just write:
[LastOfTime OUT]-[FirstOfTime In]

If either field was null this would return null, otherwise it will return the difference.
 
Upvote 0
Thanks I am getting there slowly
I am now at least getting an answer. but I don't think it is correct.

The goal is to subtract the last time an employee swiped through a door from the first time they swiped in an get the total number of hours/minutes they were in that room.

LastOfTime Out = 1/13/2020 5:25:16 PM minus FirstOfTime In = 1/13/2020 6:39:10 AM is giving me an result of 0.448680555498868

I was looking more for more like 11 hours and 10 minutes approx.

Thanks for taking the time to help

L
 
Upvote 0
Isn't that just under 11 hours, not over? Your result is the fractional part of the day. You'll have to do math on it - like multiply by 24. If you Use Int function on the calc, you can get the hours. To get the minutes, I think you'll have to multiply the remainder by 60. If you just round up the fractional part, no one will realize that the digits represent the fraction of a day and not minutes themselves.
 
Upvote 0
^ correct. Result is is 44.868056 percent of a day. This result is measured in day. You can convert it to minutes or hours or seconds as you require. Or even to a "time" or "date" (which it essentially is already).
 
Upvote 0
Maybe this is what you need:
int(.448680555498868*24) & ":" & format(.448680555498868, "nn:ss")

Result should be 10:46:06
Substitute .44.. part with your calculation
 
Upvote 0
Thank you very much Micron for you help, I believe that is working perfectly. I will work with it and post back, but on my initial try it looks good

L
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top