Hours worked calculation

[Guest]

LUNCH BREA
Day In Out In Out Total Hours Worked
Monday 9:00 00:00 00:00 5:00 ###############

I have the above spread sheet and have the formula to calculate the total hours worked. What I have noticed is that some employees do not have a lunch break and therefore there is only time in and time out hours but my formula will not calculate that. How would I calculate a time in (the first one) and a time out (the last one) without the lunch break

Thank
-Jennifer

 

[Peo Sjoblom]

To get the total hours you would need to use

=out2-in_1-(in_2-out1)

=cell_with_5:15-cell_with_9:00-(cell_with_2:30-cell_with_1:30)

using your example
--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Thanks, but the format must be - "In / Out / In / Out"

What I'm noticing is if time was entered as follows, my total formula does not work:

in out in out total
9:00 1:30 2:30 5:15 ##########

It only seems to calcualte if the break in & out are entered in the noon

hour (12). I need a formula to calculate the time in-out added to the time
in - out for a total hours worked to calcualte in the .25 increments. I
though I had it but it just isn't calculating properly... someone please
help !

 

[Peo Sjoblom]

If you are going to use AM/PM you need to enter that as well, i.e.
1:00 PM or use 13:00. To round down to nearest 15th minute use

=FLOOR(A1,1/96)

where A1 holds the value you want to round

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Thank you so much for your help.....
The times are being entered in time format w/colon. I just checked and It

doesn't look as if the times differentiate, anything after 12 defaults to am
and this is proablly where the problem lies but I don't know how to fix it.

I want to round down to the 15 minute increment - :00/:15/:30/:45
----- Basil wrote: -----

Are these times being entered in time format (strictly entered with the colon)?
Have you taken steps to differentiate between am and pm (eg use a 24 hour clock)?
As for the 15 minute increments - do you want to round up/round

down/round to nearest/prevent the user from entering anything that doesn't
end in :00/:15/:30/:45?

Don't stress yourself, because this kind of thing is very workable - we'll get there.

Basil
PS if you don't want to use a 24 hour format, you're going to have to

be quite heavy on the validation of entry - I'd recommend using a 24 hour
format.

----- Jennifer wrote: -----

Thanks, but the format must be - "In / Out / In / Out"

What I'm noticing is if time was entered as follows, my total formula does not work:

in out in out total
9:00 1:30 2:30 5:15 ##########

It only seems to calcualte if the break in & out are entered in

the noon hour (12). I need a formula to calculate the time in-out added to
the time in - out for a total hours worked to calcualte in the .25
increments. I though I had it but it just isn't calculating properly...
someone please help !

 

[Guest]

Basil,

I don't know how else to try to get in touch with you other than reply to your most recent post and hope you read it. I saw on a previous post of yours that you created a crosstab query report by modifying Duane Hookum's At Your Survey. I am trying to do the same thing, but I am having no luck with it. I would greatly appreciate it if you could let me know how you did it. Thanks

(e-mail address removed)