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.
Prashanth KumarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ravi Varma, PMPTechnical LeadCommented:
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
Alexandre MichelManager; IT ConsultantCommented:
Looking back at your original post I created the following sheet

Result
Formula
This will take into account dates as well as time
Hope this helps
Alexandre MichelManager; 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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bharat BhushanSolution ManagerCommented:
Assume you have A1 with IST

Try:

B1=A1+TIME(5,30,0)
Bill PrewIT / 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
Alexandre MichelManager; 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.