How to Convert Seconds to Hours and Minutes in Excel

We have a dataset of some projects and how long they took in seconds. We are going to convert these seconds into hours and minutes.

Method 1 – Dividing with a Numeric Value to Convert Seconds to Hours and Minutes in Excel

Steps:

=C5/(60*60*24)

Steps:

=CONVERT(C5,"sec","day")

Steps:

=TEXT((C5/86400)-INT(C5/86400),"h:mm")

Formula Breakdown:

Steps:

=INT(C5/3600)&":"&INT(((C5/3600)-INT(C5/3600))*60)

Formula Breakdown:

=INT(((C5/3600) calculates the hours and rounds them down.
=INT(((C5/3600)-INT(C5/3600))*60) calculates the minutes value by calculating the total hours as a decimal value and removing the integer part, then multiplying by 60 and rounding down.

Press the Enter button and pull the fill handle down to fill all the cells.

Steps:

=TEXT(C5/86400,CHOOSE(MATCH(C5,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))

Formula breakdown:

MATCH(C5,{0,60,3600},1) → extracts the position in an array matching the total value. C5 is the lookup_value and {0,60,3600} is the lookup_array argument from where it is matched. “1” is the match_type argument indicating the less than criteria.
The output is “3”.
CHOOSE(3,”s “”sec”””,”m “”min”” s “”sec”””,”[h] “”hrs”” m “”min”” s “”sec”””)) → in this part “3” is the index_num while “s “”sec”””,”m “”min”” s “”sec”””,”[h] “”hrs”” m “”min”” s “”sec””” represents the value1, value2, value3 inside the string.
Confirming output → [h] “hrs” m “min” s “sec”
TEXT(C5/86400,”[h] “”hrs”” m “”min”” s “”sec”””) → here it will convert a text value into number format.
The output from “C5/86400” will be shown to “[h] “”hrs”” m “”min”” s “”sec””” this format.
The output stands to “0.0512”
Our final output is 1 hrs 13 mins 45 sec.