CODEWITHSUNDEEP
excelformula
kakhaberi melikishvili
kakhaberi melikishvili

Reputation: 11

formating numbers into Time format

I want to convert numbers (that represent seconds) into HH:MM:SS format (like shown in pic..) but don't want to split them.. I want to format it using some formula or

formating method

Upvotes: 0

Views: 154

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

If you just want to display the 715 seconds as hours:minutes:seconds, you will need a helper column that you use just for display.

You can create it either as in your example screenshot, or using a formula:

=TEXT(B2/86400,"[hh]:mm:ss")

Note the brackets around hh. That keeps the hours from rolling over every 24 and displays total hours correctly for instances where seconds > 86,400.

There's no way I know of to have 715 displayed as the associated hours:minutes:seconds in the same cell.

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37119

You can use a formula like this to convert 715 to HH:MM:SS:

=TEXT(FLOOR((A1-FLOOR(A1/86400,1)*86400)/3600, 1), "00:") & text(floor((A1 - floor(A1/3600,1)*3600)/60, 1),"00:") & text(A1 - floor(A1/3600,1)*3600 - floor((A1 - floor(A1/3600,1)*3600)/60, 1)*60,"00")

Let's break it down.

Get hours

  • A1 is seconds
  • There are 86400 seconds in a day
  • FLOOR(A1/86400, 1) gives us the floor of days
  • Multiply that by 86400 will give us seconds in those days FLOOR(A1/86400,1)*86400
  • Remove those many seconds from A1 and you get seconds remaining in the fraction of the last day A1- FLOOR(A1/86400,1)*86400
  • Divide those remaining seconds with 3600 to get hours FLOOR((A1- FLOOR(A1/86400,1)*86400 )/3600, 1)
  • Use TEXT function to format it to 2 digits and a colon at the end TEXT(FLOOR((A1- FLOOR(A1/86400,1)*86400 )/3600, 1), "00:")

Then, find remaining minutes and format it.

Then, take remaining seconds and format it.

Alternate method

  • Assume that your seconds are in column A6.
  • In B6, we will put Whole Days =FLOOR(A6/86400, 1)
  • In C6, we will put remaining seconds =A6-B6*86400
  • In D6, we will put Whole Hours =FLOOR(C6/3600,1)
  • In E6, we will put remaining seconds =C6-D6*3600
  • In F6, we will put Whole Minutes =FLOOR(E6/60, 1)
  • In G6, we will put remaining seconds =E6-F6*60
  • In H6, we'll format Hours, Minutes and Seconds =TEXT(D6,"00:") & TEXT(F6,"00:") & TEXT(G6,"00")

Upvotes: 0

Related Questions