Hi,

Can we show date in the format of day and hour. For example, can we show 30 hours as 1 day 6 hours. Similarly, can we show 35 as 1 day 11 hours.

Please solve my query and do the needful.Regards,

navnit

Try this: =INT(A1/24)&" Days " & A1-INT(A1/24)*24 & " Hours"

If you want to clean it up so you don't get 1 Days and/or 1 Hours, try this:

=IF(INT(A1/24)=1,"1 Day ", INT(A1/24)&" Days ") & IF(A1-INT(A1/24)*24= 1,"1 Hour",A1-INT(A1/24)*24 & " Hours")

Format the cell as:

dd - hh:mmIf you have 61:05 (hours:minutes) in the cell, it will display as

02 - 13:05 (days - hours:minutes).Or, if you want to use a formula: (F10 is the cell that displays

the time.)

Type this into F11: (Everything is one line.)

=IF(F10="","",CONCATENATE(DAY(F10),IF(DAY(F10)=1,"

Day"," Days")," - ",HOUR(F10),IF(HOUR(F10)=1," Hour","

Hours")," : ",MINUTE(F10),IF(MINUTE(F10)=1," Minute","

Minutes")))

Hi, If you want to include text in the formatting so that it shows as say:

1 day(s) 06 hours

you have to precede any letter in the text that could be a date format with a '\'For the above example enter the format as d \d\a\y(\s) hh \h\ou\r\s

BTW [h] forces the duration to display hours only.

All of this assumes that you are entering the hours in a way that Excel recognizes as a time e.g. 30:00

Regards

Here's one more way to do it: Custom Format Cell as:

d "Day" h "Hours"

Enter in cell B1 the formula:

=CONVERT(A1,"hr","day")

If A1 contains 30, then B1 will show 1 Day 6 Hours

If A1 contains 35, then B1 will show 1 Day 11 HoursMIKE

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History