Calculate time difference to the half hour

[Ken Ivins]

I am creating an employee time sheet that the put in their starting time and
ending time (From a drop down list). I would like Excel to calculate the
amount of time showing full and if need be a half hour. My problem is that
the results half only been in full hours. Such as 9 am to 5 pm comes out as
8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5
hours.

I have tried three types of formulas with the same result:

=TEXT(F11-E11,"h")

=(INT(F10-E10*24))

=hour(f10-E10)

Any ideas on getting the results I need?

Thanks,
Ken

 

[Don Guillett]

try this
=ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0)

 

[Ken Ivins]

Don,

Thanks for your help. I tried this a few different ways with out getting
the results I'm looking for. Any other ideas?

Ken

 

[Ken Ivins]

Okay, This seemed to work.

=((TEXT(F10-E10,"h:mm"))-INT(TEXT(F10-E10,"h:mm")))*24

Anyone see a problem with this?

Thanks,
Ken

 

[Tim C]

Ken,

It's unclear what format you want the result to be in.

Try:

=INT((F10-E10)*48)/2

and format as a number, or

=INT((F10-E10)*48)/48

and format as time.

Tim C

 

[Ron Rosenfeld]

I am creating an employee time sheet that the put in their starting time and
ending time (From a drop down list). I would like Excel to calculate the
amount of time showing full and if need be a half hour. My problem is that
the results half only been in full hours. Such as 9 am to 5 pm comes out as
8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5
hours.

I have tried three types of formulas with the same result:

=TEXT(F11-E11,"h")

=(INT(F10-E10*24))

=hour(f10-E10)

Any ideas on getting the results I need?

Thanks,
Ken

Assumption is that your times are entered as Excel times; eg. 9 AM; 4:30 PM;
etc.

If you wish to round to the nearest 30 minutes, then:

=ROUND((EndTime-StartTime)/TIME(0,30,0),0)*TIME(0,30,0)

If you wish to round up to the next 30 minutes, so that 7:01 --> 7:30, then

=CEILING(EndTime-StartTime,TIME(0,30,0))

and format as [h]:mm.

If you wish to convert either of the above to decimal numbers, then multiply
the result by 24:

=24 * ROUND((EndTime-StartTime)/TIME(0,30,0),0)*TIME(0,30,0)

or

=24 * CEILING(EndTime-StartTime,TIME(0,30,0))


--ron

 

[infoman]

Ken Ivinswrote

Okay, This seemed to work

Anyone see a problem with this

Thanks
Ke

Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need s
much help that you set up email accounts for each forum where yo
post ask questions so that you can keep it all straight

You run a business http://www.kivins.com charging people for the fre
help you get here. How about paying us? Why don't you learn i
yourself the way we had to? I went to school, put in time, paid mone
to learn.

We all give help to each other, but you just take. From us, from you
customers