# Excel Formula

Christopher Gore used Ask the Experts™
on
Excel Formula?

TIME: IST ( 24-11-2019 02:30:00)
I need to have EST of the same.

I have tried : =A1-TIME(9,30,0)

I get an error: #VALUE!
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
=DATE(MID(A1,FIND("-20",A1)+1,4),MID(A1,SEARCH("-??-",A1)+1,2),MID(A1,FIND("-",A1)-2,2))+MID(A1,FIND(":",A1,6)-2,8)-TIME(9,30,0)
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Rather than deal with an inscrutable formula, you may prefer to work with a user-defined function in a formula like:
=TimeConverter(A1)

Put the following code in a regular VBA module (just like it was a recorded macro). Make sure to save the file as *.xlsm.
``````Function TimeConverter(DateTimeIST As String) As Double
Dim DateTime As Double, TimeOffset As Double
Dim sDateTime As String
TimeOffset = TimeSerial(9, 30, 0)
sDateTime = Mid(DateTimeIST, InStr(1, DateTimeIST, "(") + 1)
sDateTime = Application.Trim(Replace(sDateTime, ")", ""))

DateTime = DateSerial(Mid(sDateTime, 7, 4), Mid(sDateTime, 4, 2), Left(sDateTime, 2)) + TimeValue(Right(sDateTime, 8))
TimeConverter = DateTime - TimeOffset

End Function
``````
Enterprise Solutions Architect

Commented:
solution is good but I need to show ( EST Time and Date) . basically I need to let US people know what time they are working in IST (India) time
and vise versa
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
="TIME: EST ( " & TEXT(DATE(MID(A1,FIND("-20",A1)+1,4),MID(A1,SEARCH("-??-",A1)+1,2),MID(A1,FIND("-",A1)-2,2))+MID(A1,FIND(":",A1,6)-2,8)-TIME(9,30,0),"mm-dd-yyyy hh:mm:ss)")

="TIME: EST ( " & TEXT(TimeConverter(A1),"mm-dd-yyyy hh:mm:ss)")

If I am not understanding what you want, please show sample inputs and desired results.

Do more with