Time Zone equation

[skylinekiller]

I have a flight record that i need to have time zone converted locally from UTC

In F4 I have the UTC date/arrival time
I would like G4 to have local (which is plus 9 hours from UTC) time of date/arrival time

So should look like this

F4=14-Nov-2020/1700
G4=15-Nov-2020/0200 (automatically populated from formula once F4 has data inputted)

When you add 9 hours it takes you to the next day at 0200
The date format is flexible.

Thank you

 

[Peter_SSs]

If the day part of your original arrival time is always 2 digits (eg 01-Nov-2020/1700) then try

=IF(F4="","",LEFT(F4,11)+REPLACE(RIGHT(F4,4),3,0,":")+9/24)

If the day is only single digit at the start of the month then try

=IF(F4="","",LEFT(F4,LEN(F4)-5)+REPLACE(RIGHT(F4,4),3,0,":")+9/24)

In either case format the result column with the custom format of dd-mmm-yyyy/hhmm

 

[skylinekiller]

Thank you. that seemed to work. Let me fiddle with it a little.

 

[Peter_SSs]

You're welcome. Post back with details if any problems arise.

 

[Rick Rothstein]

Here is another formula that works on my US locale system (not sure it it works in non-US locales)...

=IFERROR(9/24+REPLACE(SUBSTITUTE(F4,"/"," "),LEN(F4)-1,0,":"),"")