How can I change time data from minutes:seconds to hours:minutes without changing the data?

Hi Arturo,

3:15 is ambiguous. It could be either a duration of 3 hours and 15 minutes, or a time of day as 3:15 am.

I would forget about forcing a new Duration display. Break the 3:15 into 2 numbers, then recombine them as a Duration that you want. As follows:

Formula in C2 is TEXTBEFORE(B2,":")

Formula in D2 is TEXTAFTER(B2,":")

Formula in E2 is DURATION(weeks,days,C2,D2)

(Or you can use commas as placeholders for weeks and days: DURATION(,,C2,D2)

Some more examples:

I prefer the unambiguous format Style of h m

If you prefer, you can change that to 0:00

I would be interested to hear if Quickbooks is giving you any hour values greater than 12. Such information may clear up the 'Date&Time' versus 'Duration' format that you are importing.

Regards,

Ian.

Arturo Hernandez2 wrote:

Is there a way that I can stop Numbers from converting the data when I change the format? I am working with hundreds of entries from monthly reports

There is no way, it appears, to stop the unwanted conversion.

But a short script can "reconvert" hundreds of entries to what you want without setting up an extra column each time.

1. Copy-paste below script into Script Editor (in Applications > Utilities.
2. Select the multiple cells that you need to convert. (Don't change the Data Format first. Let the script do the work).
3. Click the triangle 'run' button in Script Editor.

This should take about a minute to test.

If "nothing happens" then you may need to give permission by switching on 'Script Editor' at System Settings > Privacy & Security.

Test on a copy of your data to make sure that the script is doing what you want.

If necessary change the "h" and "m" abbreviations in the 'set value' line of the script to match the language in the Region and Language settings on your Mac. For example, Italian might need "o" instead of "h" for hour.

SG

tell front document of application "Numbers"
	tell active sheet
		tell (first table whose class of selection range is range)
			repeat with c in cells of (get selection range)
				set format of c to duration
				set temp to value of c -- assume is min instead of sec
				set hrs to temp div 60
				set min to temp mod 60 as integer
				set value of c to (hrs & "h" & " " & min & "m" as text)
			end repeat
		end tell
	end tell
end tell

I've been trying to think of a simple solution that keeps that same format and doesn't require a temporary column but haven't come up with anything yet. So here is a way that requires a temporary column and some manual labor

1. When I import a CSV with values like "3:15", they show up in Numbers as "automatic" format but the underlying format is text. It looks like this is what you are getting also. Lets say that these values are in column A.
2. Make a temporary new column. In that column use the formula =A*60. Depending on your version of Number this will spill down to the other rows or you will have to fill down to complete the column of formulas. The results should be numbers that equal the number of minutes.
3. Select and copy all of those results.
4. Paste Formula Results back to column A. This is a command in the Edit menu.
5. Format column A as duration
6. Delete the temporary column