# wanting to do a timesheet

#### MrOnly

##### New Member
ok working on a time sheet
i wanted teh time sheet to take the emp start time from the top and use it
for the first entry when you enter a time it took it adds to the start time that duration of entry and makes a end time
then it checks then next lien and if therer is a duration it adds that to the end time and fills in the start and end time of each task

this is all working well
but i also want to factor in lunch so i ahve a place to enter start and end time for lunch and then i subtract them and return a time for lunch and i want to have the 'IF' command check to see if = "LUNCH" and if so take enter the start and stop times on the line = to the lunch data

the pre lunch code i came up with in not vba just in cell if but its getting to hard to see what is going to happen when it starts getting this long so i was thinking vba so it was less crammed together but im stumped on the call form a differnt sheet and several other issues

here is my excel sheet so you can look at the code layout ect
HTML:
``https://1drv.ms/f/s!AgOUefGjOY_Ze-wizge6eEM9tds``

HERE is the vba i was working on
Private Sub minusLunch()​

Dim LUNCH As interger​
LUNCH = b8​
If Range("B18") = Time(0, 0, 0) Then​
Range(“d17”).Value = ""​
ElseIf Range("B17") > Time(0, 0, 0) Then​
Range(“d17”).Value = “”​
End Sub​

THANKS in ADV ### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### MrOnly

##### New Member
non vba way not working
=IF(\$B17=TIME(0,0,0),"",IF(\$B17>TIME(0,0,0),SUM(B17:C17),"",if(\$b17='LUNCH'),sum(\$b17:lunch_TIME),"")))
ok working on a time sheet
i wanted teh time sheet to take the emp start time from the top and use it
for the first entry when you enter a time it took it adds to the start time that duration of entry and makes a end time
then it checks then next lien and if therer is a duration it adds that to the end time and fills in the start and end time of each task

this is all working well
but i also want to factor in lunch so i ahve a place to enter start and end time for lunch and then i subtract them and return a time for lunch and i want to have the 'IF' command check to see if = "LUNCH" and if so take enter the start and stop times on the line = to the lunch data

the pre lunch code i came up with in not vba just in cell if but its getting to hard to see what is going to happen when it starts getting this long so i was thinking vba so it was less crammed together but im stumped on the call form a differnt sheet and several other issues

here is my excel sheet so you can look at the code layout ect
HTML:
``https://1drv.ms/f/s!AgOUefGjOY_Ze-wizge6eEM9tds``

non vba way not working
=IF(\$B17=TIME(0,0,0),"",IF(\$B17>TIME(0,0,0),SUM(B17:C17),"",if(\$b17='LUNCH'),sum(\$b17:lunch_TIME),"")))

HERE is the vba i was working on
Private Sub minusLunch()​

Dim LUNCH As interger​
LUNCH = b8​
If Range("B18") = Time(0, 0, 0) Then​
Range(d17).Value = ""​
ElseIf Range("B17") > Time(0, 0, 0) Then​
Range(d17).Value = ​
End Sub​

THANKS in ADV non vba way not working
=IF(\$B17=TIME(0,0,0),"",IF(\$B17>TIME(0,0,0),SUM(B17:C17),"",if(\$b17='LUNCH'),sum(\$b17:lunch_TIME),"")))

#### Joe4

##### MrExcel MVP, Junior Admin
Welcome to the Board!

In your original post, all sorts of issues here (highlighted in red):

Private Sub minusLunch()
Dim LUNCH As interger
LUNCH = b8
If Range("B18") = Time(0, 0, 0) Then
Range(d17).Value = ""
ElseIf Range("B17") > Time(0, 0, 0) Then
Range(d17).Value = “”
End Sub

Summary of issues:
- Integer is not spelled correctly
- If LUNCH is an Integer, it cannot be b8. Maybe you meant:
LUNCH=Range("B8")
- Excel VBA does not like slanted double-quotes. You must use the straight up and down ones.

In your second post, the end of your formula is not correct:
=IF(\$B17=TIME(0,0,0),"",IF(\$B17>TIME(0,0,0),SUM(B17:C17),"",if(\$b17='LUNCH'),sum(\$b17:lunch_TIME),"")))
The range reference
\$b17:lunch_TIME is not valid.

#### MrOnly

##### New Member
Welcome to the Board!

In your original post, all sorts of issues here (highlighted in red):

Private Sub minusLunch()
Dim LUNCH As interger
LUNCH = b8
If Range("B18") = Time(0, 0, 0) Then
Range(d17).Value = ""
ElseIf Range("B17") > Time(0, 0, 0) Then
Range(d17).Value = “”
End Sub

Summary of issues:
- Integer is not spelled correctly
- If LUNCH is an Integer, it cannot be b8. Maybe you meant:
LUNCH=Range("B8")
- Excel VBA does not like slanted double-quotes. You must use the straight up and down ones.

In your second post, the end of your formula is not correct:
=IF(\$B17=TIME(0,0,0),"",IF(\$B17>TIME(0,0,0),SUM(B17:C17),"",if(\$b17='LUNCH'),sum(\$b17:lunch_TIME),"")))
The range reference
\$b17:lunch_TIME is not valid.

OK the weird slanted quoted not sure where they came from but will correct i used the same key to type both ....

ok this is what i ahve now been working on for this cell

=IF(\$B17=TIME(0,0,0),"",IF(\$B17>TIME(0,0,0),SUM(B17,C17),IF(\$B17>TIME(0,0,0),SUM(TimeSheet!C17,Time!B8),"")))

but it still donest sum the c17 from sheet timesheet! with b8 form sheet time!

d17 is still showing 8 am instead of 9 but if i put a time in it does add b17,c17 and put in d17
but if i just use =sum(TimeSheet!C17,Time!B8) it sums the 2 cells

#### MrOnly

##### New Member
this works also but is kindof messy

=IF(\$B17="Lunch",SUM(\$C17,VLOOKUP(\$B17,Time!\$A4:\$B11,2,FALSE)),SUM(\$C17,\$B17))

Replies
6
Views
58
Replies
6
Views
55
Replies
1
Views
71
Replies
2
Views
376
Replies
0
Views
169

Threads
1,127,201
Messages
5,623,343
Members
415,969
Latest member
Rey99

### Share this page ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back