Converting data between csv, Access, Excel and ArcGIS can be a hassle, especially when dates and times are involved.
One of today’s dilemmas was how to convert decimal time into 24 hour time in ArcGIS.
Decimal times converts as follows:
1 = 24:00, 0.75 = 18:00, 0.5 = 12:00, and 0.729167 = 17:30.
In Excel you just change the data format. But today I spent what seemed like hours trying to figure out the formula so I could do it in ArcGIS. This time I didn’t want to resort to importing the data back into Excel and into stuffing around trying to get it to export how I wanted it and then importing back into ArcGIS etc…
Anyway. Here is how to calculate it in ArcGIS (or anything else really).
Given a field with the existing decimal time ([DecTime]). Create fields to store the [Hour] and [Minute].
Calculate as follows:
[Hour] = Int([DecTime]*24)
[Minute] = Int([DecTime*24) - [Hour])*60)
Now you can combine the hour and minute parts of the 24 hour time however you want. For example,
[Time24] = [Hour] * 100 + [Minute]
or
[Time24] = [Hour] & “:” & [Minute]
2 responses so far ↓
martin roundill // November 5, 2009 at 2:29 pm |
You could also calculate the [Minute] without needing to precalculate the [Hour] by using:
[Minute] = (DecTime*1440) mod 60
This also gives a better answer than your original solution which truncates the minute rather than rounding eg if the minute value prior to taking the interger value wa 42.99 your calculation would return 42 rather than 43.
To adjust your calcuation to return the nearest minute you could just add 0.5 before taking the integer value.
eg [Minute] = Int(([DecTime*24) - [Hour])*60)+0.5)
Suze // November 5, 2009 at 3:33 pm |
Thanks for the info Martin!