| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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? |
|
#2
|
|||
|
|||
|
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? |
|
#3
|
|||
|
|||
|
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? |
|
#4
|
|||
|
|||
|
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? |
|
#5
|
|||
|
|||
|
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? |
|
#6
|
|||
|
|||
|
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? |
|
#7
|
|||
|
|||
|
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? |
|
#8
|
|||
|
|||
|
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? |
|
#9
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time Sheet | Excel Discussion (Misc queries) | |||
| Formula to deduct unpaid breaks in time sheet | Excel Discussion (Misc queries) | |||
| time sheet calculations | Excel Worksheet Functions | |||
| Excel formula for a time sheet | Excel Worksheet Functions | |||
| Time Sheet calculations | Excel Worksheet Functions | |||
Linear Mode
