Convert HH:MM to DD:HH:MM in Excel 1

[jdegeorge]

Hi

I have 2 cells. A1 is formatted as hh:mm[/color green] and contains the value 44:37[/color green] and A2 is formatted as dd:hh:mm[/color green] and the formula

 =(a1)*24/8

, where the "8" represents that there are 8 hours in a working day.

The resulting value in A2 is 05:13:51[/color green] when I'm expecting it to be 05:04:37[/color green] (that's 5 days 4 hours and 37 minutes).

Seems that the # of days is correct, but I haven't a clue how to get the hours and minutes correct.

In another cell formated to GENERAL, I tried this formula

=CONCATENATE(HOUR(A1/8)

, which returns the correct number of days (5). I'm thinking that I could possibly do something with the MINUTES() function on the minute value of A1 to display the remaining fractional minutes but can't figure out how to reduce it by the minutes represented by the DAYS.

HELP!

Jim DeGeorge (MS Access Programmer)

 

[TonyJollans]

44 hours and 37 minutes times 3 (24/8) is 133 hours and 51 minutes or 5 days 13 hours and 51 minutes which is what Excel is giving you. Why do you want a different answer and what rules are there for getting it?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

Time is part of Date and is a fraction of a day. There may be 8 hours in a Working Day, but Time has 24 hours in a day.

Format is just icing on the cake. You must deal with the real values.



Skip,
[sub]
[red][/red]
[/sub]

 

[GlennUK]

I have to agree with Skip, in that you are dealing with real times, and not working days. To take the hours and minutes you have in A1, and convert into working days use this:

=TEXT(INT(A1*24/8),"0")&" working days, "&HOUR(A1-INT(A1*24/8)*8/24)&" hours "&(A1*24-INT(A1*24))*60&" minutes"

Or, you may wish to alter the time value to be the equivalent time serial that would result in 5:04:37, like this ( NOT really recommended, as the number is meaningless, being a fudge to get something to display as you want ):

=(INT(A1*24/8)+HOUR(A1-INT(A1*24/8)*8/24)/24+(A1*24-INT(A1*24))/24)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jdegeorge]

Tony

I'm thinking in terms of an 8-hour day, so 44 hours and 37 minutes would be 5 days (40 hours) with a remainder of 4 hours and 37 minutes. I was so caught up in that line of thinking that it didn't dawn on me that Excel wasn't!

Thanks for the clarification.

Jim DeGeorge (MS Access Programmer)

 

[jdegeorge]

Skip...Thanks for the clarification.

Jim DeGeorge (MS Access Programmer)

 

[jdegeorge]

Glenn

Bingo! That's exactly what I wanted to see! I agree with you, Skip and Tony that I'm not working with real times, but the basis of the calculation is to track vacation hours accrued for each pay period. I needed to translate those into whole days, so every 8 hours of accrued time equals one vacation day.

Thanks to you all!

Glenn...enjoy the star.

Jim DeGeorge (MS Access Programmer)

 

[GlennUK]

Glad to have been of some help to you!

Thanks for the star ( I always enjoy stars ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.