Excel iso 8601 timestamp subtract 2 hours convert

0
votes

I have a large amount of date/time fields that need to be changed in excel. I need to subtract 2 hours from the current date/time format ISO 8601.

Ex: 2006-09-21 16:45:00 +0000 <----Changed to------>2006-09-21 14:45:00 +0000

I was able to parse the data into separate columns and subtract 2 hours using this formula: =B1+TIME(2,0,0) and then concentrate the columns back.

I ran into a problem when the time is 01:59:99 or less. It gives me #######

Anyone have a formula to subtract 2 hours from ISO 8601 format. Please and thank you.

2
excelexcel-formuladatetime-formatdata-migrationiso8601
Just add that value back to your date and it should display properly. Excel won't display negative times (when using the 1900 date system), but the actual value (fraction of a day) is maintained. – Ron Rosenfeld

2 Answers

0
votes

If you want to do this in a single formula, you can use:

A1:  your_ISO_datetime
B1:  =LEFT(A1,19)-TIME(2,0,0)
   with custom format: yyyy-mm-dd hh:mm:ss "+0000"

enter image description here

0
votes

This should help, if your original value is in Cell A2:

=IF(NUMBERVALUE(MID(A2,12,2))<2,LEFT(A2,8)&TEXT(NUMBERVALUE(MID(A2,9,2))-1,"00")&" "&TEXT(NUMBERVALUE(MID(A2,12,2)-2+24),"00")&RIGHT(A2,12),LEFT(A2,8)&TEXT(NUMBERVALUE(MID(A2,9,2)),"00")&" "&TEXT(NUMBERVALUE(MID(A2,12,2)-2),"00")&RIGHT(A2,12))

Thanks, Tim