Time Sheet frustrations

[Meelikki]

Hi -

I am trying to create a time sheet that has the start and end times per day,
with total hours scheduled for the week that includes 30 for lunch if they
are scheduled on a particular day for more than 6 hours. Total hours would be
the scheduled time minus 30 minutes for lunch if applicable.

Monday Tuesday
Employee Total Start time End Time Start time End time
Kathy 39.5 8:00 6:00 11:00 3:30
Brad 35.25 9:00 4:00 8:00 5:00

any suggestions?

 

[Bob Phillips]

Assuming that Kathy is in row 3, the total is in column B, and start/end
times are in C3:L3, try this total formula

=SUM((MOD(COLUMN(C3:L3),2)=0)*C3:L3)-SUM(((MOD(COLUMN(C3:L3),2)=1)*C3:L3))-S
UM(--(IF(MOD(COLUMN(D3:M3),2)=0,D3:M3)-IF(MOD(COLUMN(C3:L3),2)=1,C3:L3)>0.25
))/48
it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

I should have added that the cell should also be formatted as [h]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Meelikki]

awesome...thank you for your quick response!

I should have added that the cell should also be formatted as [h]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

Assuming that Kathy is in row 3, the total is in column B, and start/end
times are in C3:L3, try this total formula

=SUM((MOD(COLUMN(C3:L3),2)=0)*C3:L3)-SUM(((MOD(COLUMN(C3:L3),2)=1)*C3:L3))-S
UM(--(IF(MOD(COLUMN(D3:M3),2)=0,D3:M3)-IF(MOD(COLUMN(C3:L3),2)=1,C3:L3)>0.25
))/48
it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


would

 

[Meelikki]

I am getting a value error, I have reformatted the cell, and I have entered
the formula as you indicated. You have D3:M3, is that correct?

 

[Bob Phillips]

Yes it is correct, it is deliberate so that the cells subtracted line-up. It
might be word-wrap, so check with this

=SUM((MOD(COLUMN(C3:L3),2)=0)*C3:L3)-SUM(((MOD(COLUMN(C3:L3),2)=1)*C3:L3))-
SUM(--(IF(MOD(COLUMN(D3:M3),2)=0,D3:M3)-IF(MOD(COLUMN(C3:L3),2)=1,C3:L3)>0.2
5
))/48

Did you also array-enter?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Meelikki]

yes, I did array enter the formula. I will recheck my entering of the
formula..

 

[Meelikki]

I appreciate all the help that you have given me on this time sheet! Thank
you very much..there are two things missing, is there anyway to have it
deduct the .5 hours if they work longer than 6 hours in a day..and second, I
would like the total to be in real number format..example would be that they
are working for 39.25 hours per week.

 

[Bob Phillips]

Meeliki,

It already does the first part, subtracts .5 hours if more than 6 per day
worked.

For the second part, just multiply by 24, and format as General.

--

HTH

RP
(remove nothere from the email address if mailing direct)