TIME CALCULATION

[Guest]

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.

 

[Guest]

Format the cells using the following custom format, which is located at the
bottom of the custom format listing:

[h]:mm:ss

 

[Niek Otten]

=B1-A1+D1-C1
Format the result cell as [h]:mm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.

 

[Dave O]

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.

 

[Guest]

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

 

[Guest]

Thank you, I did exactly what you instructed me to do but why em i getting
the answer as #VALUE?

 

[David Biddulph]

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

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

=B1-A1+D1-C1
Format the result cell as [h]:mm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.

 

[David Biddulph]

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.