Excel day and month

Microsoft Book: office excel 2003 step b...
August 13, 2009 at 11:04:03
Specs: Windows XP
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.


See More: Excel day and month

Report •

August 13, 2009 at 12:09:02
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")

Report •

September 30, 2009 at 07:55:27
Format the cell as:
dd - hh:mm

If 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.)
Day"," Days")," - ",HOUR(F10),IF(HOUR(F10)=1," Hour","
Hours")," : ",MINUTE(F10),IF(MINUTE(F10)=1," Minute","

Report •

September 30, 2009 at 08:33:37

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


Report •

Related Solutions

September 30, 2009 at 10:28:34
Here's one more way to do it:

Custom Format Cell as:

d "Day" h "Hours"

Enter in cell B1 the formula:


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



Report •

Ask Question