Hi friends,
Iām download the previous data in my dashboard, date and time show UTC time in excel like this(Sun Apr 08 2018 13:24:55 GMT+0000 (UTC) ). how can i convert this date and time convert to Indian standard time please help me.
on the dashboard, it shows IST time but when downloaded it shows as UTC. thank you for reporting this.
How to change download file UTC time to IST time.
i will report this to the team and see if there is a solution.
thanks for helping shramiksalgaonkar.
I think that is a feature, not a bug 
Once you hit the database, you need one time, not all these timezones, with and without adjustments.
When you download the csv, and are analyzing with Excel, you can use the following to convert:
=(-6*3600+MID(A2,17,2)*3600+MID(A2,20,2)*60+MID(A2,23,2))/(24 * 3600)
The -6 is for Mountain Daylight Time.
For Indian Standard Time, change the -6 to +5.5, I think.
2 Likes
thank you @jameszahary this will help alot of user. I was also trying the same approach but failed at a point.
it should be -5.3.
1 Like
I use this conversion on my excel =(-5.3*3600+MID(A2,17,2)*3600+MID(A2,20,2)*60+MID(A2,23,2))/(24 * 3600)
but the Time not change this type of value i get 0.216701
format the cell (or column) as Time ā¦ you get 5:12:03 AM
Iām not sure about that -5.3 ā¦ I think it is 5:30 ahead of UTC, or + 5.5 hours ā¦ you can fiddle with that.
1 Like
Thanks for helping now i get Indian time +5.5 is the correct conversion time
1 Like
@jameszahary thank you for correcting me. that is great @muthukumar7103. can you a share screenshot of it.
hi friends,
=(5.5*3600+MID(A2,17,2)*3600+MID(A2,20,2)*60+MID(A2,23,2))/(24 * 3600) this formula only convert to IST Time how can I convert Exact date month and Time convert Indian present date time
on my Excel
the date month should remain same. you can have a separate column for date and month.
you have to use the formula to convert time from UTC to IST every time you download data from the dashboard.
Now i download last 2 days data from my dashboard and I selected [6-5-18 12AM to 7-5-18 10AM] but downloaded .csv file showing this date Sat May 05 2018 18:35:06 GMT+0000 (UTC). also changed UTC to IST time format it gives same result.
this is becasue IST is 5:30 hrs ahead of UTC that makes 6-5-18 12AM IST to 5-5-18 18:35:06 UTC. let me see if i can do anything also @jameszahary can you help in this.
The selection is in local time, and the result is UTC ā seems to work fine.
May 6 2AM ā May 7 5AM returns
May6 8AM UTC ā May 7 11AM UTC ā¦ and correct with -6 hours for my timezone
Here is the code to convert the month day forward or back a few hours - as you see the date changes at 6AM UTC for my timezone of -6. Paste this into a cell and format as Date, and change -6 as required.
=DATE(MID(A2,12,4),MONTH(1&MID(A2,5,3)),MID(A2,9,2))+(-6*3600+MID(A2,17,2)*3600+MID(A2,20,2)*60+MID(A2,23,2))/(24 * 3600)
1 Like
thank you @jameszahary
Update for new date time format
=DATE(MID(A2,2,4),MID(A2,7,2),MID(A2,10,2))+(-6*3600+MID(A2,13,2)*3600+MID(A2,16,2)*60+MID(A2,19,2))/(24 * 3600)
This contains time and date in one cell ā the -6 is for Mountain Daylight Time zone
2 Likes