Subtracting two date/time fields

Liz_I3

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,882
Office Version
365
Platform
Windows
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
625
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,687
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
625

ADVERTISEMENT

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,882
Office Version
365
Platform
Windows
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,687
Office Version
2013
Platform
Windows

ADVERTISEMENT

^ 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,882
Office Version
365
Platform
Windows
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
625
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,264
Messages
5,510,204
Members
408,779
Latest member
Lermiapolar

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top