CODEWITHSUNDEEP
excelexcel-formula
Otiel
Otiel

Reputation: 18743

Format a time span as a number of days, hours and minutes

This is purely an Excel sheet question.

I have a time span that is basically a difference between two cells, each containing a date:

I have managed to get the time difference in number of hours and minutes by changing the number format to [h]:mm. Now I would like to get the difference in number of days, hours and minutes. I tried to set the number format to [d]:[h]:[mm] but it is not accepted by Excel.

Here's what I have now:

    A                   B                   C (=A2-A1)
    ----------------    ----------------    ----------
1|  14/10/2011 00:00    17/10/2011 07:50    79:50
2|  14/10/2011 00:00    17/10/2011 11:00    83:00

Here's what I would like to get:

    A                   B                   C (=A2-A1)
    ----------------    ----------------    ----------
1|  14/10/2011 00:00    17/10/2011 07:50    3:7:50
2|  14/10/2011 00:00    17/10/2011 11:00    3:11:00

How can I do that? Is there a number format available for that?

Upvotes: 24

Views: 108556

Answers (7)

Buddika Weerasinghe
Buddika Weerasinghe

Reputation: 33

Had a similar kind of question, except for me, I needed hours divided by 8, instead of 24, as I needed working days. So I came up with a solution, but it's really really complex to understand because the code looks like a mess, but if you segment them, it's not that hard.

=CONCAT(INT(C16*24/8),":",IF(INT((C16-INT(C16))*24)<10,CONCAT("0",INT((C16-INT(C16))*24)),INT((C16-INT(C16))*24)),":",IF(MINUTE(C16)<10,CONCAT("0",MINUTE(C16)),MINUTE(C16)))
  • Note that I have used "8" to divide the hours by 8 because I needed working hours. So you can neglect that if you do not want it
  • I needed to display the result as "4:09:08" rather than "4:9:8". (This is in "Days:Hours:Minutes" format). That's why I have used extra IF() and CONCAT() functions inside.

Upvotes: 0

Jose Alberto Salazar
Jose Alberto Salazar

Reputation: 89

=IFERROR(INT(Date1-Date2)&" Days "&INT(MOD((Date1-Date2);INT(Datw1-Date2))*24)&" Hours "&MINUTE(Fate1-Date)&" Min";IFERROR((Date1-Date2);"NA"))enter code here

I think this should do the trick, whatever date you have.

Upvotes: 0

Gary
Gary

Reputation: 71

Unfortunately it appears number and datetime formats cannot be combined, otherwise a custom format of:

0:h:m

would be the ticket. However, if for spans of 32 days or more, you are satisfied with just displaying the number of (fractional) days you can use a custom format like:

[>=32] 0.00 "days"; d:h:m

The cell value remains numeric.

Upvotes: 7

TimZ
TimZ

Reputation: 1

The following approach works as well, assuming that your start date is in cell C2 and your end date is in cell D2:

 =TEXT((D2-C2)-MOD(D2-C2,1),"0") & " days " & TEXT(MOD(D2-C2,1),"hh:mm")

Upvotes: 0

brettdj
brettdj

Reputation: 55672

You can use TEXT
=TEXT(B1-A1,"d:h:mm")

Note the same effect can be achieved using a simple number format on the cells directly

  • select your range (cells C1, C2 etc)
  • right click and Format Cells
  • Custom
  • Type d:hh:mm

If unlike your example data, your date differences exceed 31 days, then an approach such as =INT(B1-A1)&":"&TEXT(B1-A1,"h:mm")
will work

Upvotes: 34

inetpro
inetpro

Reputation: 111

The following is certainly not the shortest formula and not a direct answer to the question but works to show the correct result (also on LibreOffice) in the following format:

# days ## hours ## minutes ## seconds

=CONCATENATE(TEXT(FLOOR(B2-A2,1),"@")," days ", IF(HOUR(A12)-HOUR(A11)<0,HOUR(A12)-HOUR(A11)+24,HOUR(A12)-HOUR(A11))," hours ",IF(MINUTE(A12)-MINUTE(A11)<0,MINUTE(A12)-MINUTE(A11)+60,MINUTE(A12)-MINUTE(A11)), " minutes ", TEXT(B2-A2,"s"), " seconds")

Upvotes: 0

Bill Barry
Bill Barry

Reputation: 3523

Warning: the above only works for ranges less than 31 days. use 

=CONCATENATE(TEXT(FLOOR(B1-A1,1),"@")," Days",TEXT(B1-A1," h:mm:ss"))

instead for ranges above 31 days. This will not sort well, so it would be better to do the calculation in one column and then prettify that column for the end result.

Upvotes: 9

Related Questions