UTC time convert to IST time

Ideas / Suggestions
1

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.

2

on the dashboard, it shows IST time but when downloaded it shows as UTC. thank you for reporting this.

3

How to change download file UTC time to IST time.

4

i will report this to the team and see if there is a solution.

5

thanks for helping shramiksalgaonkar.

6

I think that is a feature, not a bug :grinning:
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:

3
3742Ɨ185 49.7 KB

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

thank you @jameszahary this will help alot of user. I was also trying the same approach but failed at a point.

image
image1366Ɨ768 119 KB

it should be -5.3.

1 Like
8

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

9

format the cell (or column) as Time ā€¦ you get 5:12:03 AM

image

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.

image
image733Ɨ341 33.3 KB

1 Like
10

Thanks for helping now i get Indian time +5.5 is the correct conversion time

1 Like
11

@jameszahary thank you for correcting me. that is great @muthukumar7103. can you a share screenshot of it.

12

this the time conversion for Indian standard Time

Time%20convert
Time%20convert1366Ɨ768 123 KB

1 Like
13

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

14

the date month should remain same. you can have a separate column for date and month.

16

you have to use the formula to convert time from UTC to IST every time you download data from the dashboard.

17

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.

18

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.

19

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

image

image
image802Ɨ183 7.18 KB

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)

image
image882Ɨ164 6.13 KB

1 Like
20

thank you @jameszahary

21

Update for new date time format

image
image1142Ɨ123 5.11 KB

=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