Want2BExcel
Board Regular
- Joined
- Nov 24, 2021
- Messages
- 112
- Office Version
- 2016
- Platform
- Windows
Help me understand this.
The set-up is as follows.
A1 (weekday)
B1 (date)
D1 (time,start1)
E1 (time,end1)
F1 (time, catagory1)
G1 (time,start2)
H1 (time,end2)
I1 (time, catagory2)
L1 (hours1 total in decimal (E1-D1)*24)
M1 (hours1 total in decimal (H1-G1)*24)
N1 (hours total total in decimal (L1+M1)
Q1 (value of a day-off in decimal hours) -7,4
R1 (used saved overtime hours)
And here is my formula
=IF(ISBLANK(D1);0;IF(A1="Thursday";MIN(-7+L1+M1+R1+Q1);0))
When I have set time it works fine, but for days where time is still not filled in, the result should be 0 (because of the ISBLANK) and it is working for all other days than Thursday. This day gives result -8,88178E-16 and I don't understand. I mean -7+7 should be 0, but it don't. I think the problem lies in the MIN(-7+
Why? Because Thursday is the only weekday where it is -7
All other days the formula has either -8, -7,4 or -6 (it is to subtract expected hours on a given weekday and result in over- or under time)
Hope this makes sense to somebody! Please ask if you need more info to solve this
Regards
Kenneth
The set-up is as follows.
A1 (weekday)
B1 (date)
D1 (time,start1)
E1 (time,end1)
F1 (time, catagory1)
G1 (time,start2)
H1 (time,end2)
I1 (time, catagory2)
L1 (hours1 total in decimal (E1-D1)*24)
M1 (hours1 total in decimal (H1-G1)*24)
N1 (hours total total in decimal (L1+M1)
Q1 (value of a day-off in decimal hours) -7,4
R1 (used saved overtime hours)
And here is my formula
=IF(ISBLANK(D1);0;IF(A1="Thursday";MIN(-7+L1+M1+R1+Q1);0))
When I have set time it works fine, but for days where time is still not filled in, the result should be 0 (because of the ISBLANK) and it is working for all other days than Thursday. This day gives result -8,88178E-16 and I don't understand. I mean -7+7 should be 0, but it don't. I think the problem lies in the MIN(-7+
Why? Because Thursday is the only weekday where it is -7
All other days the formula has either -8, -7,4 or -6 (it is to subtract expected hours on a given weekday and result in over- or under time)
Hope this makes sense to somebody! Please ask if you need more info to solve this
Regards
Kenneth