# Subtracting two date/time fields

#### Liz_I3

##### Well-known Member
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Micron

##### Well-known Member
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
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
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
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
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
^ 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
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
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

Replies
1
Views
237
Replies
2
Views
696
Replies
3
Views
412
Replies
7
Views
442
Replies
11
Views
1K

1,181,598
Messages
5,930,800
Members
436,761
Latest member
mintwaxed

### 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.

### Which adblocker are you using?

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

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