Log In
Register
Home
New Member
January 11, 2020
Offline
Hi,
I am having some trouble developing a formula to assign point values based on minutes tardy on the timesheet spreadsheet attached. Can you please help?
I would like for F8 to populate if C8 > B3. For example if C8 > B3 then F8 = .5.
Then essentially the same thing for leaving early. If E8>D3 then G8 populates a certain value.
Is it possible to make F8 & G8 populate differently depending on how late the punch is?
1-4 minutes late = .5
5-29 minutes = 1.0
30 minutes to 1 hour 59 minutes = 1.5
Thanks for your help!
August 21, 2019
Offline
Use Power Query is easier
Attached example
August 21, 2019
Offline
Hi Joey,
for excel formula
use
=IF(AND((C8-B3)*24*60>=1,(C8-B3)*24*60<=4),0.5,IF(AND((C8-B3)*24*60>=5,(C8-B3)*24*60<=29),1,IF(AND((C8-B3)*24*60>=30,(C8-B3)*24*60<=119),1.5)))
make sure that F8 is under General in Format
New Member
January 11, 2020
Offline
Is there a specific format I need to use for B3 and C8? When I enter a time into C8, F8 shows FALSE instead of a point value. Thanks again for your time and help!
August 21, 2019
Offline
Hi Joey
because they are more than 1 hour 59 minutes, copy and paste the following formula, those that exceed 119 will be 2 points
=IF(AND((C8-B3)*24*60>=1,(C8-B3)*24*60<=4),0.5,IF(AND((C8-B3)*24*60>=5,(C8-B3)*24*60<=29),1,IF(AND((C8-B3)*24*60>=30,(C8-B3)*24*60<=119),1.5,2)))
1 Guest(s)
