change date time zone

[ijw]

I receive from my broker in the States a financial report that shows transaction dates in American EST. As I'm in Australia this is somewhat annoying. Is there a way, perhaps adding an extra column, that converts this time to Australian Eastern Standard Time?

Regards,
ijw

[JBeaucaire]

You don't specify your exact needed format with an example. Does this link give you something you can work with:

http://www.mvps.org/dmcritchie/excel...ime.htm#t2cols

[JBeaucaire]

For the date in cell B2, the following three formulas allow you to decipher the pieces:

Please Login or Register  to view this content.

Now you can piece all those together in a Date formula, like so...ugly, but all in one cell:

=DATE(RIGHT(B2,4)*1,TEXT(DATE(1,MONTH((1&LEFT(TRIM(B2),3))),1),"m")*1,TRIM(RIGHT(LEFT(TRIM(B2),FIND(" ",TRIM(B2),1)+2),2))*1)

or if you used helper cells to capture those pieces:

=DATE(E2,D2,C2)

[daddylonglegs]

I'm assuming you need to add 15 hours to convert from US EST to Australia EST. With transaction date in b2 you can use this formula in a blank column to convert and add 15 hours

=TRIM(MID(B2,FIND(":",B2)-5,2)&LEFT(B2,FIND(" ",B2,2)-1)&RIGHT(B2,5)&MID(B2,FIND(":",B2)-3,9))+15/24

This is a valid excel date/time and as such you can format any way you want, see column S on attached

[ijw]

Guys, thank you so much for your your help. daddylonglegs, your spreadsheet works perfectly. An adjustment for daylight savings and Bob's your Uncle.
"Actually, I am a rocket scientist.". jb, that has to be the quote of the day. Just waiting now for "Actually, I am a brain surgeon." I am neither, so thank you both very much.
Cheers
ijw