specific date format can't convert from EST to GMT

[jolinchew]

I have the EST time (2023-08-09T14:23:19.000-0400) in B2 and need to convert to GMT in the format of (2023-08-09T18:23:19.000+0000) in D2.
in my excel file, B2=2023-08-09T14:23:19.000-0400, I define D2=left(B2,19)+TIME(4,0,0) but it is not converting to GMT. anything wrong? thanks

[AliGW]

Is your profile correct? Still using Excel 2003?

Can't troubleshoot without some sample data.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Glenn Kennedy]

A file would be helpful, as we have no idea if yur string is text that looks like a number, or is an actual number.

[TMS]

If it is a true date and time, the underlying value will be a number with 5 digits before the decimal point and 5+ digits after it.

Again, if that is the case, you should be able to copy the value (=B2) and subtract 4/24 and format as required.



Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[JohnTopley]

Try (assuming TEXT string)



[FORMULA]="(" & TEXT(DATEVALUE(MID(B2,2,10))+TIMEVALUE(MID(B2,13,8))+TIME(4,0,0),"yyyy-mm-ddThh:mm:ss")& ".000+0000)"[\FORMULA]

[jolinchew]

The formula shows error, pls see attached.

[HansDouwe]

Please try:

Please Login or Register  to view this content.