wanting to do a timesheet

[MrOnly]

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

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

 

[MrOnly]

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

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]

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]

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]

this works also but is kindof messy

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