Format code for a date with time zone in excel

excel date-formatting
Farhana picture Farhana · Oct 28, 2014 · Viewed 13.9k times · Source

I have date value with a time zone.

24 Nov 2005 GMT+05:30

What is the format code for this custom format in Excel?

I tried dd MMM yyyy with z,zz,zzz, and Z but Excel ignores those options.

Answer

kjexley picture kjexley · Oct 28, 2014

Not sure if possible. I've just gone through every letter in custom format cell and nothing looked right.

What I would suggest is maybe splitting it up into a few cells and then bringing them all together.

In the first column (A1) you could have the date, formatted how you like (dd mmm yyyy).

Second (B1) you could have the time before you account for the different timezone (formatted how you like, i.e. hh:mm:ss.

Third (C1) define if the timezone is ahead or behind with a plus/minus sign.

Fourth (D1) you could set the time difference (i.e. your example +5:30). Format the cell as h:mm.

Fifth (E1), enter the formula: =IF(C1="+",B1+D1,IF(C1="-",B1-D1)). Format the cell as hh:mm:ss.

Finally, (F1) there's two options for you. You can combine the date and time adjusted to the timezone, or you can combine the date, original time and include timezone.

The first option would be: =TEXT(A1,"dd mmm yyyy ")&TEXT(E2,"hh:mm:ss")

Second would be: =TEXT(A1,"dd mmm yyyy ")&TEXT(B1,"hh:mm:ss ")&"GMT"&TEXT(C1,"")&TEXT(D1,"h:mm")

This is the absolute best solution I could think of. Sorry if it's not what you asked for.

Related questions

Excel TEXT formula doesn't convert 'yyyy' to a year

I want to combine some text with a date in Excel 2013. Let's say I have a cell A2 with a date like 30-10-2014. I tried to append the date after the text with this formula: ="Some text and a …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More