convert from 0.1212323 to hh:mm:ss

[shahnawaz_khan]

hi all

while converting xlsx to csv time format got changed from 00:00:00 to 0.000123433. Kindly help to convert from 0.000123433 to hh:mm:ss or 00:00:00

[LindonB · Accepted solution]

Hi there. Numerical conversions with times and dates come up from time to time with Excel.

I'm unclear on if you have 0.1212323 (header) or 0.000123433 (description). If you have 0.1212323, number you have actually represents 2:54:34 AM. Otherwise, it is 12:00:11 AM.

Either way, you can see what the number represents by multiplying the number by hours in the day and seconds in a minute to get the number of minutes after midnight: 0.1212323*60*24 = 174.574512.

I'm assuming you have a datetime, not just a time. If so and you don't expect] to have a time other than 00:00:00, you can use DateTimeTrim([Field1],'day'). Otherwise, you can use DateTimeAdd("1900-01-01", [Field1]*60*24, "minutes") in a formula tool and set it as time. 

[SPetrie · Accepted solution]

Use a formula tool and the todatetime() function. Set the output field to be a time value if you only want the time, or a date/time if you need both.

[LindonB]

Ah, that saves some steps :)

[shahnawaz_khan]

Hi,

This formula is working DateTimeAdd("1900-01-01", [Field1]*60*24, "minutes")

But getting result only in Minutes. We want it in HH:MM:SS.

Again CSV file attached.

We want to calculate AHT of every call.

[shahnawaz_khan]

hi,

i am still not able to convert into time format can you please check the attached CSV file try to convert.

[SPetrie]

If you are trying to calculate AHT, getting it into a time format wont quite do what you want. You would end up with something like 00:12:34 which is a time of day for 12 minutes and 34 seconds past midnight, not a length of time of 12 minutes and 34 seconds. You cant average the time of day, but you can average a length of time. I think a more involved formula to give you a total number of seconds would be more beneficial since you can average that and converting to a H:M:S format isnt that difficult to do afterwards.

I converted from the original Talk Time field to seconds using this formula. 12/30/1899 is the date it evaluated to so I used that as the starting point for the difference calculation.

DateTimeDiff(ToDateTime([talk time]),"1899-12-30 00:00:00","Seconds")

Once you have the number of seconds, you can average them out to get your average handle time.

Once you have the average handle time, you can add that time in seconds back to a midnight value to get it into a prettier H:M:S format depending on how you want it to look.