Converting Seconds to H:MM from query

[Kayda]

Hi:

I have a query that returns a value in seconds and I would like to
format the cell so that it gives H:MM. I tried the format of [H]:mm,
but that seems to assume that the value is entered is in days, so if
the value is "1", then it gives 24:00. What I want is that if the
value returned is 5400, it shows 01:30. Do I have to change things on
the query side?

Thanks,
Kayda

 

[Dave Peterson]

I don't know if it's easier to modify your query or adjust the values in excel.

You could either use a formula like:

=A1/24/60/60
(and format as hh:mm:ss)

Or you could put
=24*60*60
(or 86400)
in an empty cell

Copy that cell
Select the range to fix
Edit|Paste special|check divide)
Format that range as hh:mm:ss
and clean up that cell with 86400 in it.

Hi:

I have a query that returns a value in seconds and I would like to
format the cell so that it gives H:MM. I tried the format of [H]:mm,
but that seems to assume that the value is entered is in days, so if
the value is "1", then it gives 24:00. What I want is that if the
value returned is 5400, it shows 01:30. Do I have to change things on
the query side?

Thanks,
Kayda

 

[Kayda]

Actually, now if I bring the values in as days and format as [h]:mm it
seems to work and I can SUM the column. Only problem now is the
rounding, if the [h]:mm:ss format would be 01:33:59, the [h]:mm format
turns out as 01:33. Is there any way to get that to round? Also, the
way it is now, does the SUM I do add up the full [h]:mm:ss values in
the column and then do the (incorrect) rounding, or does it add up all
the [h]:mm only and not count the seconds? I'm assuming the former
since the actual "number of days" value is actually stored in the
field and we are just talking formatting here.

Thanks,
Kayda

 

[Dave Peterson]

I'm not sure how bringing it in as days converts it to hours, minutes, seconds.

But =sum() will add up all the values in the range with no rounding.

So if you wanted to round up those to the nearest minute, you could use
something like:

=Ceiling(sum(a1:a10),time(0,1,0))

Give it a temporary format of: [hh]:mm:ss
to see if it works.

There is also an =floor() to round down.

Actually, now if I bring the values in as days and format as [h]:mm it
seems to work and I can SUM the column. Only problem now is the
rounding, if the [h]:mm:ss format would be 01:33:59, the [h]:mm format
turns out as 01:33. Is there any way to get that to round? Also, the
way it is now, does the SUM I do add up the full [h]:mm:ss values in
the column and then do the (incorrect) rounding, or does it add up all
the [h]:mm only and not count the seconds? I'm assuming the former
since the actual "number of days" value is actually stored in the
field and we are just talking formatting here.

Thanks,
Kayda