wanting to do a timesheet

MrOnly

New Member
Joined
Aug 31, 2017
Messages
5
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
:)
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

MrOnly

New Member
Joined
Aug 31, 2017
Messages
5
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
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 31, 2017
Messages
5
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
Joined
Aug 31, 2017
Messages
5
this works also but is kindof messy

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

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,844
Members
415,861
Latest member
Leetor72

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
Top