Difference Between Times Fields (1 Viewer)

[ErinL]

Hello -

I feel like I should know how to do this but can't seem to figure it out. :banghead:

I have two fields in a query "Goal Time" and "Actual Time". They are imported from an Excel spreadsheet and the imported data type is short text.

The data in these fields is equal to the amount of time it should have taken them to complete an assignment versus the actual time it took them. The format is hh:nn:ss so a goal time of 00:17:55 means they should have completed the assignment in 17 minutes 55 seconds.

I am trying to do a simple calculated field that shows the difference between the actual time and the goal time. I have tried converting to different formats but it returns either #Error or #Func!.When I do Time Value it actually converts it to an AM/PM format.

What I want to see is a goal time of 00:17:55, an actual time of 00:17:00, a difference of 00:00:55.

How can I get it to return the difference between the two fields?

Thank you in advance!

 

[theDBguy]

Hi. After importing the data from Excel, what did you set the data types to? If DateTime, what happens if you do something like [Goal]-[Actual] and format it to Short Time?

 

[ErinL]

Hello! I didn't specify on the first import and it imported them as short text fields. I imported a second time and changed the format of the fields to the "Date with Time" option and it didn't import any of the data from those fields. An Import Errors table was created and the error was "Type Conversion Error".

 

[isladogs]

Dates and times are stored as double numbers so convert to double, do the subtraction, then convert back to date

CDate(CDbl(#5:00:55 PM#) - CDbl(#5:00:00 PM#))

Result = 00:00:55

 

[ErinL]

I tried this and it returns the #Error.

The fields are not "time" in the sense of AM or PM. They are only an hh:nn:ss format of how long of a period of time.

 

[theDBguy]

I tried this and it returns the #Error.

The fields are not "time" in the sense of AM or PM. They are only an hh:nn:ss format of how long of a period of time.

Hi Erin. Can you post a sample db?

 

[isladogs]

Yes I understand that but Access automatically converts times such as #17:00:55# to #5:00:55 PM#. The result is the same as date/time values are, as I said, stored as double numbers.

However, whilst I was typing my first response you stated the values are actually text fields. If its possible I suggest reimporting as date/time fields and my expression will then work

 

[ErinL]

Here is a small sample of what is in the original database.

 

[theDBguy]

Hi. Thanks! Here it is back...

 

[ErinL]

Well dang that was fast! Thank you so much!!

 

[theDBguy]

Well dang that was fast! Thank you so much!!

Hi. You're welcome. It's easier when we have something to play with. Colin and I were happy to assist. Good luck with your project.