Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ken Ivins
 
Posts: n/a
Default Calculate time difference to the half hour

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


  #4   Report Post  
Ken Ivins
 
Posts: n/a
Default

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



"Ken Ivins" wrote in message
...
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




"Don Guillett" wrote in message
...
try this
=ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0)

--
Don Guillett
SalesAid Software

"Ken Ivins" wrote in message
...
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








  #5   Report Post  
Tim C
 
Posts: n/a
Default

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

"Ken Ivins" wrote in message
...
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



"Ken Ivins" wrote in message
...
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




"Don Guillett" wrote in message
...
try this
=ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0)

--
Don Guillett
SalesAid Software

"Ken Ivins" wrote in message
...
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












  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 25 Jan 2005 11:46:09 -0500, "Ken Ivins" wrote:

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
  #7   Report Post  
infoman
 
Posts: n/a
Default

Ken Ivinswrote:


Okay, This seemed to work.

Anyone see a problem with this?

Thanks,
Ken




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

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

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i calculate the difference in time? Roze Excel Worksheet Functions 15 September 11th 06 08:36 AM
calculate difference in time to hours Chris Excel Worksheet Functions 5 January 18th 05 07:07 PM
calculate negative or positve difference in time kpmoore Excel Discussion (Misc queries) 2 January 5th 05 02:35 AM
How do I calculate Costs from a Time. ie 16:00 - 12:00 = 4:00hrs x £5.50rate per hour = £22.00 Eric Nicoll Excel Worksheet Functions 2 December 10th 04 01:15 PM
Time / Formula to look at time difference carl Excel Worksheet Functions 5 November 8th 04 07:59 PM


All times are GMT +1. The time now is 01:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"