ExcelBanter - Calculating time

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Calculating time (https://www.excelbanter.com/new-users-excel/88663-calculating-time.html)

Calculating time

I know this question has been asked before, but the answers that I have found
do not seem to work for me for some reason. I am probably doing something
wrong. What I need to do is calculate hours worked for the day. I have set
up a spreadsheet to include the following:
C3 is the time in
D3 is the time out
E3 is .5 hours (this is lunch)
F3 is the total number hours worked for the day. The formula I have put in
this cell is: =HOUR(D3-C3)-E3 [this is exactly how the formula is typed in]

Now, if someone put their time in as 7:30 a.m. and their time out at 4:00
p.m. and they take the .5 hour lunch, I need F3 to reflect an 8 hour day. If
they put their time in as 7:00 a.m. and time out as 4:00 p.m. with a .5 hour
lunch, I need F3 to reflect an 8.5 hour day, and so on.

Am I doing something wrong?

Thanks!!

Teri.

Calculating time

Hi Teri,

Easier with European time, but essentially the same:

C3: 7:30
D3: 16:00
E3: 0:30

=D3-C3-E3

Gives you 8:00

--
Kind regards,

Niek Otten

"Teri" wrote in message ...
|I know this question has been asked before, but the answers that I have found
| do not seem to work for me for some reason. I am probably doing something
| wrong. What I need to do is calculate hours worked for the day. I have set
| up a spreadsheet to include the following:
| C3 is the time in
| D3 is the time out
| E3 is .5 hours (this is lunch)
| F3 is the total number hours worked for the day. The formula I have put in
| this cell is: =HOUR(D3-C3)-E3 [this is exactly how the formula is typed in]
|
| Now, if someone put their time in as 7:30 a.m. and their time out at 4:00
| p.m. and they take the .5 hour lunch, I need F3 to reflect an 8 hour day. If
| they put their time in as 7:00 a.m. and time out as 4:00 p.m. with a .5 hour
| lunch, I need F3 to reflect an 8.5 hour day, and so on.
|
| Am I doing something wrong?
|
| Thanks!!
|
| Teri.

Calculating time

Niek,

Thank you so much for getting back to me so quickly! I have changed my
formula to what you suggested and F3 returns #VALUE!. I have all the cells
EXCEPT F3 formatted as time in the 24-hour clock format (I believe that is
what you meant be "European time"). I figured this was going to be a very
simple spreadsheet, but has turned into a headache!

Thank you again!

Teri.

"Niek Otten" wrote:

Hi Teri,

Easier with European time, but essentially the same:

C3: 7:30
D3: 16:00
E3: 0:30

=D3-C3-E3

Gives you 8:00

--
Kind regards,

Niek Otten

"Teri" wrote in message ...
|I know this question has been asked before, but the answers that I have found
| do not seem to work for me for some reason. I am probably doing something
| wrong. What I need to do is calculate hours worked for the day. I have set
| up a spreadsheet to include the following:
| C3 is the time in
| D3 is the time out
| E3 is .5 hours (this is lunch)
| F3 is the total number hours worked for the day. The formula I have put in
| this cell is: =HOUR(D3-C3)-E3 [this is exactly how the formula is typed in]
|
| Now, if someone put their time in as 7:30 a.m. and their time out at 4:00
| p.m. and they take the .5 hour lunch, I need F3 to reflect an 8 hour day. If
| they put their time in as 7:00 a.m. and time out as 4:00 p.m. with a .5 hour
| lunch, I need F3 to reflect an 8.5 hour day, and so on.
|
| Am I doing something wrong?
|
| Thanks!!
|
| Teri.