<div>
=DATE(MID(A1,FIND(&quot;-20&quot;,A1<wbr />)+1,4),MID<wbr />(A1,SEARCH<wbr />(&quot;-??-&quot;,A1<wbr />)+1,2),MID<wbr />(A1,FIND(&quot;<wbr />-&quot;,A1)-2,2<wbr />))+MID(A1,<wbr />FIND(&quot;:&quot;,A<wbr />1,6)-2,8)-<wbr />TIME(9,30,<wbr />0)
</div>


=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)

Enterprise Solutions Architect

Software Squirrel

<div>
Rather than deal with an inscrutable formula, you may prefer to work with a user-defined function in a formula like:<br />
=TimeConverter(A1)<br />
<br />
Put the following code in a regular VBA module (just like it was a recorded macro). Make sure to save the file as *.xlsm.<br />

<pre><code id="code-20-42983286-1">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, &quot;(&quot;) + 1)
sDateTime = Application.Trim(Replace(sDateTime, &quot;)&quot;, &quot;&quot;))

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

End Function</code></pre>
</div>


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

<div>
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<br />
and vise versa
</div>


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

<div>
<div class="content wysiwyg-content">
Excel Formula?<br />
&nbsp;<br />
TIME: IST ( 24-11-2019 02:30:00)<br />
&nbsp;I need to have EST of the same.<br />
<br />
I have tried : =A1-TIME(9,30,0)<br />
<br />
I get an error: #VALUE!
</div>
</div>


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!

Excel Formula

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.