Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
larry_saudi
 
Posts: n/a
Default Calculate hours worked

Greetings!

We are using the 12 hour clock format in our time sheet and were able to
calculate the hours worked, however, we want to display the time 4:00 PM as
4:00 without the PM with the same calculation result.
A B C D E
IN OUT IN OUT HOURS
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.0

We use the formula in column E =(D-C-INT(D-C))*24+(B-A-INT(B-A))*24 and
format it as Number with 1 decimal places.

Using the format hh:mm in column A & B we can display the time with out the
AM/PM as it less than 1:00PM, however beyond 1:00PM
using the same format 1:00PM will become 13:00 which is not the desired time
display(in print & on screen). Typing just 4:00 will neither give a correct
answer. Pls advice. We use Excel 2003 version.

Thanks and regards




  #2   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default Calculate hours worked

Don't multiply by 24, and format as hh:mm perhaps

=(D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"larry_saudi" wrote in message
...
Greetings!

We are using the 12 hour clock format in our time sheet and were able to
calculate the hours worked, however, we want to display the time 4:00 PM

as
4:00 without the PM with the same calculation result.
A B C D

E
IN OUT IN OUT

HOURS
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.0

We use the formula in column E =(D-C-INT(D-C))*24+(B-A-INT(B-A))*24 and
format it as Number with 1 decimal places.

Using the format hh:mm in column A & B we can display the time with out

the
AM/PM as it less than 1:00PM, however beyond 1:00PM
using the same format 1:00PM will become 13:00 which is not the desired

time
display(in print & on screen). Typing just 4:00 will neither give a

correct
answer. Pls advice. We use Excel 2003 version.

Thanks and regards






  #3   Report Post  
Posted to microsoft.public.excel.newusers
larry_saudi
 
Posts: n/a
Default Calculate hours worked

Mr. Bob,

Thanks for the reply. We can now type the time and display as it is without
the AM/PM. However, using the formula we still have this problem as shown
below:
A B C D E
IN OUT IN OUT HRS WORK
Larry 7:00 12:00 1:00 4:00 8.0
Jimmy 7:30 12:00 1:00 4:00 7:30

For Jimmy the number of hours work should be 7.5 and not 7:30. How to
change/format 7:30 to 7.5 using the same formula. We compute his salary by
the number of hours work.
Thanks and best regards.

Larry


"Bob Phillips" wrote:

Don't multiply by 24, and format as hh:mm perhaps

=(D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"larry_saudi" wrote in message
...
Greetings!

We are using the 12 hour clock format in our time sheet and were able to
calculate the hours worked, however, we want to display the time 4:00 PM

as
4:00 without the PM with the same calculation result.
A B C D

E
IN OUT IN OUT

HOURS
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.0

We use the formula in column E =(D-C-INT(D-C))*24+(B-A-INT(B-A))*24 and
format it as Number with 1 decimal places.

Using the format hh:mm in column A & B we can display the time with out

the
AM/PM as it less than 1:00PM, however beyond 1:00PM
using the same format 1:00PM will become 13:00 which is not the desired

time
display(in print & on screen). Typing just 4:00 will neither give a

correct
answer. Pls advice. We use Excel 2003 version.

Thanks and regards







  #4   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default Calculate hours worked

Then use

=((D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2)))*24

and format as General


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"larry_saudi" wrote in message
...
Mr. Bob,

Thanks for the reply. We can now type the time and display as it is

without
the AM/PM. However, using the formula we still have this problem as shown
below:
A B C D E
IN OUT IN OUT HRS WORK
Larry 7:00 12:00 1:00 4:00 8.0
Jimmy 7:30 12:00 1:00 4:00 7:30

For Jimmy the number of hours work should be 7.5 and not 7:30. How to
change/format 7:30 to 7.5 using the same formula. We compute his salary by
the number of hours work.
Thanks and best regards.

Larry


"Bob Phillips" wrote:

Don't multiply by 24, and format as hh:mm perhaps

=(D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"larry_saudi" wrote in message
...
Greetings!

We are using the 12 hour clock format in our time sheet and were able

to
calculate the hours worked, however, we want to display the time 4:00

PM
as
4:00 without the PM with the same calculation result.
A B C D

E
IN OUT IN OUT

HOURS
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.0

We use the formula in column E =(D-C-INT(D-C))*24+(B-A-INT(B-A))*24

and
format it as Number with 1 decimal places.

Using the format hh:mm in column A & B we can display the time with

out
the
AM/PM as it less than 1:00PM, however beyond 1:00PM
using the same format 1:00PM will become 13:00 which is not the

desired
time
display(in print & on screen). Typing just 4:00 will neither give a

correct
answer. Pls advice. We use Excel 2003 version.

Thanks and regards









  #5   Report Post  
Posted to microsoft.public.excel.newusers
larry_saudi
 
Posts: n/a
Default Calculate hours worked

Mr. Bob,

Solved. It's a great help for us.

Thank you very much.

Larry

"Bob Phillips" wrote:

Then use

=((D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2)))*24

and format as General


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"larry_saudi" wrote in message
...
Mr. Bob,

Thanks for the reply. We can now type the time and display as it is

without
the AM/PM. However, using the formula we still have this problem as shown
below:
A B C D E
IN OUT IN OUT HRS WORK
Larry 7:00 12:00 1:00 4:00 8.0
Jimmy 7:30 12:00 1:00 4:00 7:30

For Jimmy the number of hours work should be 7.5 and not 7:30. How to
change/format 7:30 to 7.5 using the same formula. We compute his salary by
the number of hours work.
Thanks and best regards.

Larry


"Bob Phillips" wrote:

Don't multiply by 24, and format as hh:mm perhaps

=(D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"larry_saudi" wrote in message
...
Greetings!

We are using the 12 hour clock format in our time sheet and were able

to
calculate the hours worked, however, we want to display the time 4:00

PM
as
4:00 without the PM with the same calculation result.
A B C D
E
IN OUT IN OUT
HOURS
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.0

We use the formula in column E =(D-C-INT(D-C))*24+(B-A-INT(B-A))*24

and
format it as Number with 1 decimal places.

Using the format hh:mm in column A & B we can display the time with

out
the
AM/PM as it less than 1:00PM, however beyond 1:00PM
using the same format 1:00PM will become 13:00 which is not the

desired
time
display(in print & on screen). Typing just 4:00 will neither give a
correct
answer. Pls advice. We use Excel 2003 version.

Thanks and regards












  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 334
Default Calculate hours worked

I type exact formula as you did, but why em I not getting the answer to
colume E, the formula stays rather it should change to 8 hours. Can you
please send me the formula for this IN 6:00AM LUNCH 12:00 IN 12:30 OUT 5:30.

"larry_saudi" wrote:

Greetings!

We are using the 12 hour clock format in our time sheet and were able to
calculate the hours worked, however, we want to display the time 4:00 PM as
4:00 without the PM with the same calculation result.
A B C D E
IN OUT IN OUT HOURS
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.0

We use the formula in column E =(D-C-INT(D-C))*24+(B-A-INT(B-A))*24 and
format it as Number with 1 decimal places.

Using the format hh:mm in column A & B we can display the time with out the
AM/PM as it less than 1:00PM, however beyond 1:00PM
using the same format 1:00PM will become 13:00 which is not the desired time
display(in print & on screen). Typing just 4:00 will neither give a correct
answer. Pls advice. We use Excel 2003 version.

Thanks and regards




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
Number of hours worked between 18:00 and 06:00 clobns Excel Worksheet Functions 2 November 5th 05 12:19 AM
how do I calculate hours using one cell only James Fullmer Excel Worksheet Functions 2 August 28th 05 04:22 AM
Getting a total of hours & mins worked per week big_weegie New Users to Excel 1 June 16th 05 11:44 PM
calculate hours just can't figure it out monish74 Excel Worksheet Functions 1 February 13th 05 08:31 PM
CALCULATE HOURS WORKED Calculation of hours worked. Excel Discussion (Misc queries) 3 January 20th 05 07:01 PM


All times are GMT +1. The time now is 11:25 AM.

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"