#ERROR when subtracting 2 times

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
Hello
I am working in MS Access 16 on the query grid I have 2 time fields LastOfTime Out and FirstOfTime In I want to create a Time Spent field by subtract the 2 times

Time Spent: [LastOfTime Out] - [FirstOfTime In] Would be like this 4:07:00 PM-8:05:00 AM I get #Error

Thanks
L
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
Your two time fields, how exactly are they formatted in the underlying table where they are stored?
What data type are the variables?
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
HI
In the original table everything is text. I converting the time in a query using Time In: IIf([IN]="IN",TimeValue([Time]),"") and Time Out: IIf([OUT]="OUT",TimeValue([Time]),"") Then in a second grouped by query using the first one as it source I got the first time logged in and the last time logged out. Now I want to subtract the two times
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
What does the original data look like, in text format?
I want to try to recreate your scenario here on my end.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
HI
Its a report we get from our security office Records the number of time each employee swipes their access card to get in and out of a door.
We are trying to get the first time each employee swiped in and last time they swiped out and subtract the 2 for time spent in the warehouse

Part of the original data
Message Date/Time Message Text
1/13/2020 5:25 Admitted 'Jane, Smith' (Card: 203714) at 'Tapscott Office to Warehouse DOOR' (IN).
1/13/2020 6:13 Admitted 'Jane, Smith' (Card: 203714) at 'Tapscott Office to Warehouse DOOR' (OUT).
1/13/2020 6:14 Admitted 'Jane, Smith' (Card: 203714) at 'Tapscott Office to Warehouse DOOR' (IN).

Thank you for taking the time to look at this for me
L
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
So where exactly is the break between the fields, after the time, so that date and time are together in one field?
Or is the whole entire string one field? If so, you must have some intermediate calculations to parse those things out. What do those look like?
Also, is there any possibility that someone could clock in one day, and clock out the next (i.e. an overnight shift)? If so, you would then need to take the date into consideration as well as the time.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
Yes, Sorry there are 2 fields the date and time and the second is call Message Text In a query I created 2 fields IN and Out using this

IN: IIf(InStr(1,[Message Text],"(IN)",1)=0,"","IN") & Out: IIf(InStr(1,[Message Text],"(OUT)",1)=0,"","OUT")

Then 2 more fields
Time In: IIf([IN]="IN",TimeValue([Time]),"") and Time Out: IIf([OUT]="OUT",TimeValue([Time]),"")

I a second grouped query I used the above to get the First and Last time for each date for each employee

L
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
So the data you posted up in post 5, where exactly is the split between field1 and field2 in your table?
If the "Message Date/Time" field is the first field, and its value is "1/13/2020 5:25", why do you have the data type as Text instead of a valid Date/Time value?
If you are importing the data, you can import it that way, so it is recognized as a Date/Time.

Also, is the time in military time? If not, how to you designate AM from PM?
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
The first field is a date/time field military times is best
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
OK, now I am a little confused.

Originally you said:
In the original table everything is text
but now you saying:
The first field is a date/time field military times is best
So is the date field in the table actually Data Type Date/Time or Text?

If it is Date/Time, you shouldn't need to do anything fancy, just subtract them from each other.
Are you using the TimeValue function just to remove the Date piece of it? That shouldn't really be necessary, as if they are the same date, that part will just net out to zero, and only the difference in time will remain after the subtraction.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,159
Members
405,387
Latest member
michmichmich2020

This Week's Hot Topics

Top