CODEWITHSUNDEEP
excelms-accessexcel-formula
amart47
amart47

Reputation: 95

How to make numbers show as hh:mm (duration, not time of day)

I'm using an Access query and dumping the data into Excel. Each row contains a value showing how much time as certain task should take, but it simply gives me the number of minutes (e.g. 123).

Is it possible to either:

  1. Use an Excel formula to convert the sum of these numbers into hh:mm; or
  2. Convert the figures directly in Access?

Every time I convert it with the built-in Excel options, it gives me a time-of-day instead of duration.

Upvotes: 1

Views: 562

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

Since Access does not support the [H]:MM formatting code to show elapsed time, if you wanted to format the result in Access, you would need to use something like:

[Minutes]\60 & ":" & Format([Minutes] Mod 60,"00")

Where [Minutes] is the name of your integer-valued field containing the number of minutes elapsed.

Upvotes: 0

JvdV
JvdV

Reputation: 75850

Try:

enter image description here

Formula in B1:

=TEXT(A1/1440,"[H]:MM")

Another option would be to just calculate the devide and custom format B1 with [H]:MM

If I misunderstood and you interested in the time of day you can use:

=TIME(0,A1,0)

Upvotes: 1

Related Questions