admin管理员组文章数量:1122832
I have a table which pulls out the days of the week from a date stamp and also calculates when a change ended and when it was closed. If a change ended on Sunday, we have set hours to complete this so I am trying to create a change to check if value = Sunday, and the actual end date - completed date >72 hours then its a fail, otherwise a pass.
=IF(AA2<=TIME(72,0,0),(AND(AH2="Friday","pass","fail")))
=IF(AND(AH3="Sunday", AA3<"48"), "Fail", "Pass")
I cant get it formula to work in excel. Once working, I want to calculate if Sunday, then 48 hours, if Saturday, then 72 hours. Monday to Thursday is 24 hours. Friday and Saturday - 72 Hours, Sunday 48 Hours.
=IF(AND(D2="Sunday",(A2-B2)*24>48),"Fail",IF(AND(D2="Saturday",(A2-B2)*24>72),"Fail",IF(AND(D2="Friday",(A2-B2)*24>72),"Fail",IF(AND(D2="Thursday",(A2-B2)*24>24),"Fail",IF(AND(D2="Wednesday",(A2-B2)*24>24),"Fail",IF(AND(D2="Tuesday",(A2-B2)*24>24),"Fail",IF(AND(D2="Monday",(A2-B2)*24>24),"Fail","Pass")))))))
Results should be in Column C, either pass or fail depending on ending day and hours between col A and Col b.
I have a table which pulls out the days of the week from a date stamp and also calculates when a change ended and when it was closed. If a change ended on Sunday, we have set hours to complete this so I am trying to create a change to check if value = Sunday, and the actual end date - completed date >72 hours then its a fail, otherwise a pass.
=IF(AA2<=TIME(72,0,0),(AND(AH2="Friday","pass","fail")))
=IF(AND(AH3="Sunday", AA3<"48"), "Fail", "Pass")
I cant get it formula to work in excel. Once working, I want to calculate if Sunday, then 48 hours, if Saturday, then 72 hours. Monday to Thursday is 24 hours. Friday and Saturday - 72 Hours, Sunday 48 Hours.
=IF(AND(D2="Sunday",(A2-B2)*24>48),"Fail",IF(AND(D2="Saturday",(A2-B2)*24>72),"Fail",IF(AND(D2="Friday",(A2-B2)*24>72),"Fail",IF(AND(D2="Thursday",(A2-B2)*24>24),"Fail",IF(AND(D2="Wednesday",(A2-B2)*24>24),"Fail",IF(AND(D2="Tuesday",(A2-B2)*24>24),"Fail",IF(AND(D2="Monday",(A2-B2)*24>24),"Fail","Pass")))))))
Results should be in Column C, either pass or fail depending on ending day and hours between col A and Col b.
Share Improve this question edited Nov 26, 2024 at 9:06 Kirstie H Hunter asked Nov 22, 2024 at 14:32 Kirstie H HunterKirstie H Hunter 231 silver badge4 bronze badges 3- 1 Can you add a sample input dataset and your expected output? – Scott Boston Commented Nov 22, 2024 at 15:19
- hi Scott, I have update the question. Sample columns would be A to D. – Kirstie H Hunter Commented Nov 26, 2024 at 9:03
- @KirstieHHunter Sample data is better in tables — and examples for both "Pass" and "Fail" will help to make your example Complete and Verifiable – Chronocidal Commented Nov 26, 2024 at 13:18
1 Answer
Reset to default 0Based on your updated/changed question, I believe that the following may do what you want:
=IF((B2-A2)>VLOOKUP(WEEKDAY(A2),{1,2;2,1;3,1;4,1;5,1;6,3;7,3},2,FALSE),"Fail", "Pass")
Your hour-counts (24, 48, 72) are all integer multiples of days (1, 2, 3), so I am using the days in a hard-coded lookup table {1,2;2,1;3,1;4,1;5,1;6,3;7,3} based directly on the WEEKDAY of the Actual End:
| Weekday | Days | Hours | |
|---|---|---|---|
| 1 | 2 | 48 | |
| 2 | 1 | 24 | |
| 3 | 1 | 24 | |
| 4 | 1 | 24 | |
| 5 | 1 | 24 | |
| 6 | 3 | 72 | |
| 7 | 3 | 72 |
本文标签：
版权声明：本文标题：excel - stuck on how to create formula to check if value = Sunday AND date 1 - date 2 is greater than 72 hours show "fa 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736303144a1931782.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论