Has anyone cracked what format the time needs to be to successfully import into the Asana-created Estimated Time field?
I’ve tried using a decimal e.g. 7 and 1/2 hours expressed as 7.5, and it imports OK, but truncates anything less than 1hr, so 7.5 imports as 7h00m in Asana.
I also tried using an Excel time format [h]:mm, and this works beautifully for anything less than 24hrs, however any estimated time over 24hrs is expressed in Excel as a date/time: 60hrs in Excel is stored as “2/01/1900 12:00:00 PM”, and Asana can’t import it (it becomes an empty field in Asana).
I’ve figured it out. It seems the solution is to concatenate the time and store it as a text value in Excel, this will successfully import into the Asana Estimated Time field.
So for example, let’s say you have 34.5 hours expressed as decimal number in Excel. You have to split the value on the decimal point into 34 and 5, multiply the portion after the decimal by 6, so 5 * 6 to = 30, then assemble it as a text string by concatenating the values 34 & “:” & 30 in a text formatted cell. Excel will alert that you have a number stored as text, ignore this warning.
This results in a text formatted cell containing 34:30, this will successfully map on a CSV import to the Asana Estimated Time field.
The key is to have the cell in a text format. If it’s a date or time formatted cell, Excel will attempt to date/time or serialise the contents of the cell, which Asana can’t interpret and it messes up your CSV import.