# Excel day and month

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

See More: Excel day and month

#1
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 •

#2
September 30, 2009 at 07:55:27
 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")))

Report •

#3
September 30, 2009 at 08:33:37
 Hi,If you want to include text in the formatting so that it shows as say:1 day(s) 06 hoursyou 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\sBTW [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:00Regards

Report •

Related Solutions

#4
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:=CONVERT(A1,"hr","day")If A1 contains 30, then B1 will show 1 Day 6 HoursIf A1 contains 35, then B1 will show 1 Day 11 HoursMIKEhttp://www.skeptic.com/

Report •