Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of hours worked between 18:00 and 06:00 | Excel Worksheet Functions | |||
how do I calculate hours using one cell only | Excel Worksheet Functions | |||
Getting a total of hours & mins worked per week | New Users to Excel | |||
calculate hours just can't figure it out | Excel Worksheet Functions | |||
CALCULATE HOURS WORKED | Excel Discussion (Misc queries) |