Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
Can some please tell me the formula on how to calculate hours worked. For
example IN 6:15AM OUT 12:30PM IN 1:00PM OUT 6:00PM. I FOUND THERE IS A SIMILAR ANSWER HERE THAT CAME FROM LARRY, BUT WHEN I TYPE THE FORMULA THAT HE PROVIDED IT DOES'NT CALCULATE TO TOTAL HOURS ALTHOUGH I ALREADY FORMATED CELL A TO D TO h:mm and CELL E FORMATED TO NUMBER WITH 1 DEC... ALSO SHOULD THERE BE AN hh:mm or just an h:mm? PLEASE HELP. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
Format the cells using the following custom format, which is located at the
bottom of the custom format listing: [h]:mm:ss -- Kevin Backmann "RICK" wrote: Can some please tell me the formula on how to calculate hours worked. For example IN 6:15AM OUT 12:30PM IN 1:00PM OUT 6:00PM. I FOUND THERE IS A SIMILAR ANSWER HERE THAT CAME FROM LARRY, BUT WHEN I TYPE THE FORMULA THAT HE PROVIDED IT DOES'NT CALCULATE TO TOTAL HOURS ALTHOUGH I ALREADY FORMATED CELL A TO D TO h:mm and CELL E FORMATED TO NUMBER WITH 1 DEC... ALSO SHOULD THERE BE AN hh:mm or just an h:mm? PLEASE HELP. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
=B1-A1+D1-C1
Format the result cell as [h]:mm -- Kind regards, Niek Otten Microsoft MVP - Excel "RICK" wrote in message ... | Can some please tell me the formula on how to calculate hours worked. For | example IN 6:15AM OUT 12:30PM IN 1:00PM OUT 6:00PM. I FOUND THERE IS A | SIMILAR ANSWER HERE THAT CAME FROM LARRY, BUT WHEN I TYPE THE FORMULA THAT HE | PROVIDED IT DOES'NT CALCULATE TO TOTAL HOURS ALTHOUGH I ALREADY FORMATED CELL | A TO D TO h:mm and CELL E FORMATED TO NUMBER WITH 1 DEC... ALSO SHOULD THERE | BE AN hh:mm or just an h:mm? | PLEASE HELP. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
If I may, a tip: when you type in all caps on a newsgroup, that
constitutes yelling, as though you were very angry. That may simply be stuck cap lock key, but please be aware. Assuming your "in out in out" labels are in A1:A4 and the associated times are in B1:B4, you can get the answer with this formula: =((B2-B1)*24)+((B4-B3)*24) The answer to your sample data is 11.25 hours. The result you see at first may be something else: format the cell as a number with 2 decimal places. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
Thank you for your response, this is greate a simple formula but it works.
This is what I did. I'm still running into one problem, let say I worked for 5 days and each day I totaled 10:30 hours. How do I calculate this? I use the sum command but it does'nt give me to correct answer. PLEASE HELP 10/12 T IN OUT IN OUT 6:36 12:00 12:30 17:36 10:30 "Niek Otten" wrote: =B1-A1+D1-C1 Format the result cell as [h]:mm -- Kind regards, Niek Otten Microsoft MVP - Excel "RICK" wrote in message ... | Can some please tell me the formula on how to calculate hours worked. For | example IN 6:15AM OUT 12:30PM IN 1:00PM OUT 6:00PM. I FOUND THERE IS A | SIMILAR ANSWER HERE THAT CAME FROM LARRY, BUT WHEN I TYPE THE FORMULA THAT HE | PROVIDED IT DOES'NT CALCULATE TO TOTAL HOURS ALTHOUGH I ALREADY FORMATED CELL | A TO D TO h:mm and CELL E FORMATED TO NUMBER WITH 1 DEC... ALSO SHOULD THERE | BE AN hh:mm or just an h:mm? | PLEASE HELP. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
Thank you, I did exactly what you instructed me to do but why em i getting
the answer as #VALUE? "Dave O" wrote: If I may, a tip: when you type in all caps on a newsgroup, that constitutes yelling, as though you were very angry. That may simply be stuck cap lock key, but please be aware. Assuming your "in out in out" labels are in A1:A4 and the associated times are in B1:B4, you can get the answer with this formula: =((B2-B1)*24)+((B4-B3)*24) The answer to your sample data is 11.25 hours. The result you see at first may be something else: format the cell as a number with 2 decimal places. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
If you want to sum the 5 rows with 10:30 in each, format the result as
[h]:mm Note also that you're not as likely to get a sensible answer if you just say "it doesn't give me the correct answer" as if you say what answer it *is* giving you. I guess you're probably seeing 04:30 if you've got the cell formatted as hh:mm, as that's what's left over after the 4 whole days are removed from your answer of 52:30? -- David Biddulph "RICK" wrote in message ... Thank you for your response, this is greate a simple formula but it works. This is what I did. I'm still running into one problem, let say I worked for 5 days and each day I totaled 10:30 hours. How do I calculate this? I use the sum command but it does'nt give me to correct answer. PLEASE HELP 10/12 T IN OUT IN OUT 6:36 12:00 12:30 17:36 10:30 "Niek Otten" wrote: =B1-A1+D1-C1 Format the result cell as [h]:mm -- Kind regards, Niek Otten Microsoft MVP - Excel "RICK" wrote in message ... | Can some please tell me the formula on how to calculate hours worked. For | example IN 6:15AM OUT 12:30PM IN 1:00PM OUT 6:00PM. I FOUND THERE IS A | SIMILAR ANSWER HERE THAT CAME FROM LARRY, BUT WHEN I TYPE THE FORMULA THAT HE | PROVIDED IT DOES'NT CALCULATE TO TOTAL HOURS ALTHOUGH I ALREADY FORMATED CELL | A TO D TO h:mm and CELL E FORMATED TO NUMBER WITH 1 DEC... ALSO SHOULD THERE | BE AN hh:mm or just an h:mm? | PLEASE HELP. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
TIME CALCULATION
If you're getting #VALUE, the chances are that you haven't got times in the
relevant cells, but text. Try 6:15 AM, not 6:15AM, for example. -- David Biddulph "RICK" wrote in message ... Thank you, I did exactly what you instructed me to do but why em i getting the answer as #VALUE? "Dave O" wrote: If I may, a tip: when you type in all caps on a newsgroup, that constitutes yelling, as though you were very angry. That may simply be stuck cap lock key, but please be aware. Assuming your "in out in out" labels are in A1:A4 and the associated times are in B1:B4, you can get the answer with this formula: =((B2-B1)*24)+((B4-B3)*24) The answer to your sample data is 11.25 hours. The result you see at first may be something else: format the cell as a number with 2 decimal places. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet Calculation | Excel Discussion (Misc queries) | |||
#VALUE! in Time Calculation | Excel Worksheet Functions | |||
perform calculation only if current time is after 10 am | Excel Worksheet Functions | |||
"Manual calculation" takes less time. Why? | Excel Discussion (Misc queries) | |||
time calculation excel | Excel Worksheet Functions |