# Please provide the formula to convert IST 12:09:50 AM to UTC I tried entering the formula =A1-time(5,30,00) But this is throwing error.

I had this question after viewing excel - time conversion ( IST to EST ).

Please provide the formula to convert IST 12:09:50 AM to UTC
I tried entering the formula =A1-time(5,30,00)
But this is throwing error.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

say, if you have value of time in A1 by "Ctrl +Shift + :" then your formula should like
=A1-TIME(5,30,0)
This works for me in MS 2010
Manager; IT ConsultantCommented:
Looking back at your original post I created the following sheet

This will take into account dates as well as time
Hope this helps
Manager; IT ConsultantCommented:
And thus is you have your text in Cell A2, then the formula would be
``````=DATEVALUE(MID(A2,9,2)&" "&MID(A2,5,3)&" "&MID(A2,25,4))+TIMEVALUE(MID(A2,12,8))-5.5/24
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Solution ManagerCommented:
Assume you have A1 with IST

Try:

B1=A1+TIME(5,30,0)
IT / Software Engineering ConsultantCommented:
=A1-TIME(5,30,0)

Should work, what error are you getting?

Do you have an actual Excel date/time value in A1, or could it be a text string (that we need to do conversion on) or something else?

Upload a test workbook here that generates the error so we can see the details.

»bp
Manager; IT ConsultantCommented:
The question was "I had this question after viewing excel - time conversion ( IST to EST )" pointing to another post https://www.experts-exchange.com/questions/25605042/excel-time-conversion-IST-to-EST.html

The dates in that post were text only and in the WRONG format. The sample dates in that post were:

Tue Mar 30 10:23:46 IST 2010
Tue Mar 30 10:39:26 IST 2010
Tue Mar 30 10:41:46 IST 2010
Tue Mar 30 10:43:06 IST 2010
Tue Mar 30 10:45:06 IST 2010

The formula given B1=A1+TIME(5,30,0)  does not work as the texts above are not recognized as dates in Excel.
This is why a long formula is required to extract each component of the date / time

#a42506141 did not answer the question "Why is there an error"
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.