Subtracting two date/time fields

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
610
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,855
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.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
610
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
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
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.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
610
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
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,855
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.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
^ 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).
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,855
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
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
610
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,563
Messages
5,487,583
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top