Timesheet Formula Help Please!
 

Good Morning -

What I am looking to do is take the time in/time out and add it to the time
in/time out (lunch in the middle); allow for a rounder of 7 minutes, round
down, 8+ round up. Then add, PTO, Holiday or Bereavement leave for that day.

Here is an example:

Monday, March 17 Tuesday,
March 18
7:55am 7:52am
12:02pm 12:30pm
12:58pm
5:08pm

2.75 - PTO
Total Hours: 8.25 7.50

Total hours worked per day, should equal 7.5 or greater.

THANK YOU!

Timesheet Formula Help Please!
 

It would be easier to arrange your time-in and time-out data in
columns going across, and then have a different row for each day, but
assuming you have the date in A1, then try this with your current
layout:

=FLOOR((A3-A2+A5-A4)*24 + A7,0.25)

Will round down to 15 min increments. You should have just the hours
in A7.

Hope this helps.

Pete

On Mar 17, 12:01*pm, HRLegal
wrote:
Good Morning -

What I am looking to do is take the time in/time out and add it to the time
in/time out (lunch in the middle); allow for a rounder of 7 minutes, round
down, 8+ round up. *Then add, PTO, Holiday or Bereavement leave for that day.

Here is an example:

* * * * * * * * *Monday, March 17 * * * * * * * * * * * * * * * * Tuesday,
March 18
* * * * * * * * *7:55am * * * * * * * * * * * * * * * * * * * * * * * * 7:52am
* * * * * * * * *12:02pm * * * * * * * * * * * * * * * * * * * * * * * 12:30pm
* * * * * * * * *12:58pm
* * * * * * * * *5:08pm

2.75 - PTO
Total Hours: 8.25 * * * * * * * * * * * * * * * * * * * * * * * * * *7.50

Total hours worked per day, should equal 7.5 or greater.

THANK YOU!

Timesheet Formula Help Please!
 

Pete_UK -

Thank you so much!! One more question...is there a way to put a formula in
there as well, that will allow the time to still calculate, even if they work
only the first half the day? Say 7:30am to 12:30pm, then 3.5 hours of PTO?

Thank you!!

Jennifer

"Pete_UK" wrote:

It would be easier to arrange your time-in and time-out data in
columns going across, and then have a different row for each day, but
assuming you have the date in A1, then try this with your current
layout:

=FLOOR((A3-A2+A5-A4)*24 + A7,0.25)

Will round down to 15 min increments. You should have just the hours
in A7.

Hope this helps.

Pete

On Mar 17, 12:01 pm, HRLegal
wrote:
Good Morning -

What I am looking to do is take the time in/time out and add it to the time
in/time out (lunch in the middle); allow for a rounder of 7 minutes, round
down, 8+ round up. Then add, PTO, Holiday or Bereavement leave for that day.

Here is an example:

Monday, March 17 Tuesday,
March 18
7:55am 7:52am
12:02pm 12:30pm
12:58pm
5:08pm

2.75 - PTO
Total Hours: 8.25 7.50

Total hours worked per day, should equal 7.5 or greater.

THANK YOU!

Timesheet Formula Help Please!
 

The formula should still work if A2 is 7:30am, A3 is 12:30pm, A4 and
A5 are empty, and A7 contains 3.5 - did you try this set-up?

Pete

On Mar 17, 2:32*pm, HRLegal wrote:
Pete_UK -

Thank you so much!! *One more question...is there a way to put a formula in
there as well, that will allow the time to still calculate, even if they work
only the first half the day? *Say 7:30am to 12:30pm, then 3.5 hours of PTO?

Thank you!!

Jennifer



"Pete_UK" wrote:
It would be easier to arrange your time-in and time-out data in
columns going across, and then have a different row for each day, but
assuming you have the date in A1, then try this with your current
layout:

=FLOOR((A3-A2+A5-A4)*24 + A7,0.25)

Will round down to 15 min increments. You should have just the hours
in A7.

Hope this helps.

Pete

On Mar 17, 12:01 pm, HRLegal
wrote:
Good Morning -

What I am looking to do is take the time in/time out and add it to the time
in/time out (lunch in the middle); allow for a rounder of 7 minutes, round
down, 8+ round up. *Then add, PTO, Holiday or Bereavement leave for that day.

Here is an example:

* * * * * * * * *Monday, March 17 * * * * * * * * * * * * * * * * Tuesday,
March 18
* * * * * * * * *7:55am * * * * * * * * * * * * * * * * * * * * * * * * 7:52am
* * * * * * * * *12:02pm * * * * * * * * * * * * * * * * * * * * * * * 12:30pm
* * * * * * * * *12:58pm
* * * * * * * * *5:08pm

2.75 - PTO
Total Hours: 8.25 * * * * * * * * * * * * * * * * * * * * * * * * * *7.50

Total hours worked per day, should equal 7.5 or greater.

THANK YOU!- Hide quoted text -

- Show quoted text -