Time Sheet frustrations
 

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?

Time Sheet frustrations
 

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)


"Meelikki" wrote in message
...
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?

Time Sheet frustrations
 

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)


"Bob Phillips" wrote in message
...
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)


"Meelikki" wrote in message
...
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?

Time Sheet frustrations
 

awesome...thank you for your quick response!

"Bob Phillips" wrote:

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)


"Bob Phillips" wrote in message
...
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)


"Meelikki" wrote in message
...
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?

Time Sheet frustrations
 

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" wrote:

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)


"Meelikki" wrote in message
...
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?

Time Sheet frustrations
 

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" wrote in message
...
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" wrote:

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)


"Meelikki" wrote in message
...
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?

Time Sheet frustrations
 

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


"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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)


"Meelikki" wrote in message
...
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?

Time Sheet frustrations
 

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.

"Meelikki" wrote:

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


"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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)


"Meelikki" wrote in message
...
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?

Time Sheet frustrations
 

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)


"Meelikki" wrote in message
...
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.

"Meelikki" wrote:

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


"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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)


"Meelikki" wrote in message
...
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?