Displaying a figure as days, hours and minutes

[Ains]

Hi
I am trying to display, or convert a figure to days, hours and minutes
This is for a spreadsheet that keeps record of staff holidays which ca
include odd hours off. Each month's holiday is deducted from the tota
entitlement of minutes for the year leaving a running total of minute
left. At the bottom of the sheet I want this to show as days, hours an
minutes. I have found a formula that will display it as hours an
minutes i.e =INT(H59/60)&" hours "&MOD(H59,60)& " minutes" but need th
days shown as well (7.5 hour days). I have managed to get it to wor
out days as well but it does not deduct the day calculation befor
calculating the hours, therefore resulting in an incorrect figure. A
example I am working with would be to display 2025 minutes as 4 day
(7.5 Hour day) , 3 hours and 45 minutes. Can you help, many thanks i
advance
Ains

 

[Arvi Laanemets]

Hi

With minutes in cell H59:
number of days = INT(H59/(24*60))
number of remaining hours = INT(MOD(H59,24*60)/60)
number of remaining minutes = MOD(A1,60)

 

[Norman Harker]

Hi Ains!

One way that you should be able to understand easily:

=INT(H59/(60*7.5))& " days "&INT(MOD(H59,60*7.5)/60)& " hours
"&MOD(MOD(H59,60*7.5),60)&" minutes"

If H59 is 2025 it returns: 4 days 3 hours 45 minutes

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.