Menu
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.
882
To use the Time Zone function, first select the cell where you want the converted time to appear. Then click on the Formulas tab and select Insert Function. In the Insert Function dialog box, scroll down until you see the TIMEZONE function and select it. Click OK.
On the Home tab, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category box, click Date or Time, and then choose the number format that is closest in style to the one you want to create.
You will need to change the number format (Format Cells) in order to display a proper date. For example: =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date.
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.
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
