Coverting Days & Hours 3

[FYRGUY47]

Using Excel 2002. I have 10.3 days on a sheet. I am looking for an equation that would convert the 10.3 days to "10 Days" and "x" hours. We work under a 24hr schedule and are trying to calculate vacation days.

Thank you

Chris

 

[randy700]

If the 10.3 is in cell A1...

=CONCATENATE(INT(A1)," days",RIGHT(A1,1)*2.4," hours")

Randy

 

[FYRGUY47]

Thank you...

I have several rows that will need to be calculated...

On rows that have "9.0" days on my sheet, your calculation takes it to "9 days21.6 hours"

The same similar thing happens with 12.0 days =
"12 days4.8 hours" etc.....

Your thoughts....? thank you

 

[FYRGUY47]

Here is a copy of what it is showing me...

12.00 12 days4.8 hours
12.00 12 days4.8 hours
9.00 9 days21.6 hours
9.00 9 days21.6 hours
12.00 12 days4.8 hours
3.00 3 days7.2 hours
12.00 12 days4.8 hours
12.00 12 days4.8 hours
3.00 3 days7.2 hours
10.30 10 days2.4 hours
9.00 9 days21.6 hours

The 10.30 days should be 10 days 7.2 hours.

Thanks for your help

Chris

 

[xlhelp]

The formula should be

=CONCATENATE(INT(A1)," days ",ROUND((A1-INT(A1))*24,2)," hours")

However, this takes into account a 24 hour day. You probably have an 8 hour work day. In that case change the 24 above to whatever your workday is.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[FYRGUY47]

xlhelp...

That is exactly what I needed!

We do work a 24hr work schedule.

Perfect! Thank you!!

Chris

 

[SkipVought]

I would not recommend concatenation, as it does not result in a REAL Date/Time value.

You already have a value in DAYS on your sheet. This is already in the correct units to manipulate with Date / Time functions.

You should not have to do all these formula acrobatics to calculate what you need. Perhaps you ought to pose what you want to calculate given the known values that you have.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

FYRGUY47

Skip is absolutely right. I don`t ask why and as such, I am usually guilty of catering to what people are asking for. In the formula above you have all the ingredients of doing it right.

=Int(a1) will give you the days
and
Round((a1-Int(a1))*24,2) will give you your hours.

You can do some fancy Custom formatting to get what you want.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[FYRGUY47]

Skip & xlhelp,

Thanks for all your help today and in the past....

I am very interested in what formula you would suggest for my scenereo.

I tried earlier not to add in additional information that I thought would be useless information. I tried to get to the point. I thought that I had all the information to calculate this request, but for the life of me couldn't figure it out.

I am putting together a sheet to auto calculate each employees vacations based on their years of service and other parameters (see below). Although this is fairly simple on the surface, if an employee uses >12 days (288 hrs)of sick time in the calendar year(usually from an injury, illness, surgery, etc) their following years vacations are reduced by a pro-rated value based on years of service and typical vacations that would have been earned.

If an employee would typically earn 9 - 24hr shifts for their 2010 vacations, but was off 384 hrs(16 shifts) during the 2009 calendar year, then their vacations would calculate based on a typical work year of 122 (24hr)days/shifts per year. This gives us a daily vacation earned of .074 days. So if this employee used 384hrs - which is 96hrs over the 288 as mentioned above. 96hrs/24hrs = 4 days; 4 days x .074 = .296 (.30). This .30 represents .30 days of pro-rated reduction from the 9 days of vacation he would have had. I have all this done to this point, however my end result was 8.70 days of vacation, I needed to convert that to days and hours.

I hope you made it through all that, as mentioned above, I would love to see your formula for my scenereo.

Thanks Chris

 

[SkipVought]

FYI...

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

I will read through it Skip... Thanks

Chris

 

[SkipVought]

8.70 days of vacation, I needed to convert that to days and hours.

If this is only for display purpose, and NEVER for calculation, as the return value is TEXT...
[tt]
=INT(A2)&TEXT(A2," HH")
[/tt]
where A2 has your days of vacation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Sorry Skip,

That did nothing for me... I knew most of that. Am I missing something.....

Chris

 

[FYRGUY47]

Sorry Skip, my reading of the link you sent did nothing for me. Your post helped... Thank you. I think you posted while I was reading. Thanks

 

[FYRGUY47]

Skip,

That formula didn't do it. The return on my 8.70 days returned "8 16" with your formula.

FYI - The much earlier post that xlhelp posted is working perfect, I was just interested in learning a cleaner formula if there was one. Thanks for your time

Chris

 

[SkipVought]

If you need numeric values...
[tt]
days: B2 =int(A2)
Hours:C2 =(A2-B2)*24
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Okay, I think I got it now, thanks

You always make me think....

Thanks Skip.

Chris