Converting UTC Date Time in Excel

[CA_Traveler]

My solar controller produces data with this UTC time stamp. How can I convert this to a simpler format in Excel?

Convert these controller UTC times:

2014-12-24T23:59:04.050+00:00
2014-12-25T00:02:05.043+00:00

to the these local MST times.

3:59:04
4:02:05

Note: Although the UTC time changes dates the local time will convert to a daylight time for a single date since solar is only of interest during the day.

[CA_Traveler]

John, Thanks for the input. I'm using MSView which is a Morningstar program to collect the data from a MS MPPT60 solar controller on W7. As far as I can determine I have no control over the resulting csv file time format. So a conversion for us humans is in order. The above formula nicely does the job in Excel.

[wa8yxm]

UTC formerlly called Greenwich Mean Time.

EST (Extra Smart Time) UTC-5 hours EDST (Extra Dumb Stupid Time) -4hours
CST -6 CDST -5
MST -7 MDST -6
PST -8 PDST -7

Windows 8.1 lets you program two clocks, Set one to London time or UTC and then your computer will translate present time for you. You can eventually learn the pattern

I keep two sets of clocks in one room (Radio room) Because I use UTC for logging, I also have an app on my older (XP) boxes, have not yet put it on the 8.1 box, that puts a UTC clock in the upper corner. Thanks for the reminder.

[CA_Traveler]

neschultz wrote:
There is probably a more eloquent solution out there, but if you put your UTC time in column D and this formula in Column E, it should work during MST. Format Col E to custom hour, minute, second AM/PM

=TIME(IF(MID(D1,12,2)="00",17,IF(MID(D1,12,2)="01",18,IF(MID(D1,12,2)="02",19,IF(MID(D1,12,2)="03",20,IF(MID(D1,12,2)="04",21,IF(MID(D1,12,2)="05",22,IF(MID(D1,12,2)="06",23,MID(D1,12,2)-7))))))),MID(D1,15,2),MID(D1,18,2))

Good luck,

Norman

OK, maybe not eloquent but certainly clever to use the transistion to 00 hours and what I said about daytime solar only. It works great!

Thanks

[neschultz]

There is probably a more eloquent solution out there, but if you put your UTC time in column D and this formula in Column E, it should work during MST. Format Col E to custom hour, minute, second AM/PM

=TIME(IF(MID(D1,12,2)="00",17,IF(MID(D1,12,2)="01",18,IF(MID(D1,12,2)="02",19,IF(MID(D1,12,2)="03",20,IF(MID(D1,12,2)="04",21,IF(MID(D1,12,2)="05",22,IF(MID(D1,12,2)="06",23,MID(D1,12,2)-7))))))),MID(D1,15,2),MID(D1,18,2))

Good luck,

Norman

[jplante4]

Unix time is the number of seconds since the epoch (January 1,1970 - midnight GMT) I don't think that's what the OP is looking for. I think what you want is =HOUR(cell#)-6 (or -7 during MDT)

[sch911]

See if this helps....