Ryan Saari
New Member
- Joined
- Aug 4, 2008
- Messages
- 6
I need help converting this if statement I created to a "FORMULA" in vba.
This formula is used to subtract unpaid breaks from total hours worked in a day.
For Example:
J5 = 5:00:00am
H5 = 8:30:00am
Formula will conver J5 to pm, then subtrack J5 from H5 = 8.5 hours and then based on the hours worked of 8.5 subtracts 1 hour for an unpaid lunch.
=IF(J5=0, 0, IF((((J5+0.5)-H5)*24)<=4,(((J5+0.5)-H5)*24)-0,IF((((J5+0.5)-H5)*24)<6.5,(((J5+0.5)-H5)*24)-0.5,IF((((J5+0.5)-H5)*24)>=6.5,(((J5+0.5)-H5)*24)-1,IF((((J5+0.5)-H5)*24)>8.5,"OVR","")))))
My VBA code using FORMULA(START,END) results in nothing but errors.
Help!
This formula is used to subtract unpaid breaks from total hours worked in a day.
For Example:
J5 = 5:00:00am
H5 = 8:30:00am
Formula will conver J5 to pm, then subtrack J5 from H5 = 8.5 hours and then based on the hours worked of 8.5 subtracts 1 hour for an unpaid lunch.
=IF(J5=0, 0, IF((((J5+0.5)-H5)*24)<=4,(((J5+0.5)-H5)*24)-0,IF((((J5+0.5)-H5)*24)<6.5,(((J5+0.5)-H5)*24)-0.5,IF((((J5+0.5)-H5)*24)>=6.5,(((J5+0.5)-H5)*24)-1,IF((((J5+0.5)-H5)*24)>8.5,"OVR","")))))
My VBA code using FORMULA(START,END) results in nothing but errors.
Help!