#ERROR when subtracting 2 times

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
647
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Your two time fields, how exactly are they formatted in the underlying table where they are stored?
What data type are the variables?
 
Upvote 0
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
 
Upvote 0
What does the original data look like, in text format?
I want to try to recreate your scenario here on my end.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
The first field is a date/time field military times is best
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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