Subtracting dates that are in text format (to the hour)

[Crevalle]

I need to subtract one date from another in order to find the time difference between the two (to the hour). The date fields are each in a separate table. The data is in "text" format, and looks like the following:

2001-08-01-10.03.04.000000 (this is 08/01/2001 10am, I think)

I would like to subtract two dates, and get a number like 12.7 (days). This obviously means I will only need year/mo/day/hours.

I've been trying to use an update query to change the existing field format to 13 digits with this:

Format(Left([TCR_R_INQ_STATUS].[UPDATE_DATE],13),"mm/dd/yyyy hh")

It does change the date to 13 digits, but when I try to subtract the dates, I get "#error" messages in the field.

Any help would be greatly appreciated. I've been trying to figure this out for hours.

 

[Ed2020]

Something like this will reformat the date into the correct format. Do this for each date/time and the subtract one from the other:

You'll need to replace "2001-08-01-10.03.04.000000" with the relevant value or field reference.

cdate(mid("2001-08-01-10.03.04.000000",9,2) & "/" & mid("2001-08-01-10.03.04.000000",6,2) & "/" & left("2001-08-01-10.03.04.000000",4) & " " & mid("2001-08-01-10.03.04.000000",12,2) & ":" & mid("2001-08-01-10.03.04.000000",15,2) & ":" & mid("2001-08-01-10.03.04.000000",18,2))

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

To clarify, once you've subtracted the two reformatted values you will then need to use the format function to give you the answer in hours.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Crevalle]

Ed--thanks for the quick reply. Although, I'm not sure I completely follow what you are saying.

This is what I have in the first query field:

Field1: CDate(Mid("Field1",9,2) & "/" & Mid("Field1",6,2) & "/" & Left("Field1",4) & " " & Mid("Field1",12,2) & ":" & Mid("Field1",15,2) & ":" & Mid("Field1",18,2))

The other date field is named "Field2" and I did the same thing (just changing the name of the field in the formula).

I get a "data type mismatch in criteria expression" error.

 

[Crevalle]

I just saw your second post.

So are you saying I would leave the simple subtraction formula [field2]-[field1], and then use the above formula?

 

[Crevalle]

Holy $*&%* it works!

I just realized that I was using " instead of [].

THANKS!!!!!!!!!!!!!!!!!!!!!!

 

[Crevalle]

I'm not able to subtract the dates now, even though they are in the correct format (6/7/2004 11:56:52 PM). This is due to the fact that they are still formatted as "text." Aside from literally going into the table and changing the format (I get an error that says I don't have enough memory to change the format on all of the date fields), is there another way to do the subtraction?

 

[p27br]

i have the same type of problem.
Having periods stored as text (ex : 062004 and 072004) and want to use 062004 > 072004

 

[KenReay]

Having periods stored as text (ex : 062004 and 072004) and want to use 062004 > 072004

something like

If Mid(Period1,3,4)& Left(Period1,2) > Mid(Period2,3,4)& Left(Period2,2) Then

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[p27br]

thanks for that ken