Excel Formula

Christopher Gore
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical 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)
byundtMechanical 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

Open in new window

Christopher GoreEnterprise Solutions Architect

Author

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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial