A Question About Time

  • Thread starter Thread starter Junior
  • Start date Start date
J

Junior

Hi. I would like to do the following:

Insert the time I clock in one cell and in the adjacent cell insert
the time I clock out. Can this be done with key strokes?

In the third cell I would like to automatically calculate how long I
worked in hours and minutes. (Time out minus time in) Also, can this
answer be rounded off in quarter hour increments?

Thanks for any assistance.

Junior
 
Hi
not sure what you mean with 'key strokes' but if you enter your times
e.g. like 08:00 and 16:00 in A1 and B1 enter the following formula in
C1
=B1-A1
and format this cell as time (goto 'Format - Cells')
if your working time can span midnight enter the following in C1:
=B1-A1+(B1<A1)

For the rounding part use instead of the above the following formulas
=ROUND((B1-A1)*24*4,0)/(24*4)
or
=ROUND((B1-A1+(B1<A1))*24*4,0)/(24*4)
 
In a blank cell say A1 click inside making it the current cell then press
simultaneously
Control+Shift+; (the semi-colon).
Do the same again in cell B1
in Cell C1 enter = B1-A1
then Format Cell C1 - Custom (in Box type in [hh]:mm )
 
Hi
not sure what you mean with 'key strokes' but if you enter your times
e.g. like 08:00 and 16:00 in A1 and B1 enter the following formula in
C1
=B1-A1
and format this cell as time (goto 'Format - Cells')
if your working time can span midnight enter the following in C1:
=B1-A1+(B1<A1)

For the rounding part use instead of the above the following formulas
=ROUND((B1-A1)*24*4,0)/(24*4)
or
=ROUND((B1-A1+(B1<A1))*24*4,0)/(24*4)
In a blank cell say A1 click inside making it the current cell then press
simultaneously
Control+Shift+; (the semi-colon).
Do the same again in cell B1
in Cell C1 enter = B1-A1
Click to expand...
then Format Cell C1 - Custom (in Box type in [hh]:mm )
Click to expand...

Hi. Thanks for the help. I have a question. If I enter the time
manually, say 11:00 and 1:00, I can't come up with the correct answer
in the third cell using either method. The answer I get with the
formula =B1-A1+(B1<A1) is 14:00 and the answer I get with B1-A1 and
formatting with h:mm is ################.

So in other words if I forget to enter the time with the keystroke
method and have to put it in manually later I can't get the third cell
to accurately tell me how many minutes (hours) I've worked.

Thanks.

Junior
 
Hi
problem is that 1:00 is interpreted as 1:00 am. So if you'd either
enter
1:00 am
or
13:00

the formulas should work


--
Regards
Frank Kabel
Frankfurt, Germany
Hi
not sure what you mean with 'key strokes' but if you enter your times
e.g. like 08:00 and 16:00 in A1 and B1 enter the following formula in
C1
=B1-A1
and format this cell as time (goto 'Format - Cells')
if your working time can span midnight enter the following in C1:
=B1-A1+(B1<A1)

For the rounding part use instead of the above the following formulas
=ROUND((B1-A1)*24*4,0)/(24*4)
or
=ROUND((B1-A1+(B1<A1))*24*4,0)/(24*4)
In a blank cell say A1 click inside making it the current cell
then press simultaneously
Control+Shift+; (the semi-colon).
Do the same again in cell B1
in Cell C1 enter = B1-A1
Click to expand...
then Format Cell C1 - Custom (in Box type in [hh]:mm )
Click to expand...

Hi. Thanks for the help. I have a question. If I enter the time
manually, say 11:00 and 1:00, I can't come up with the correct answer
in the third cell using either method. The answer I get with the
formula =B1-A1+(B1<A1) is 14:00 and the answer I get with B1-A1 and
formatting with h:mm is ################.

So in other words if I forget to enter the time with the keystroke
method and have to put it in manually later I can't get the third cell
to accurately tell me how many minutes (hours) I've worked.

Thanks.

Junior
Click to expand...
 
Back
Top