• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula Problem

jhoncena33

New Member
Greetings everyone,
I want to calculate P and OD in the given Excel. I also want to calculate holidays only if they fall between either P or OD at both ends. If there is VL at either end, then I do not want to calculate holidays. Please keep in mind that there may be more than one holiday back to back. i want to solve this problem only with formula.

example 01

20-05-2023​
P
21-05-2023​
Holiday
22-05-2023​
OD

total days 03

example 02


25-05-2023​
VL
26-05-2023​
VL
27-05-2023​
Holiday
28-05-2023​
Holiday
29-05-2023​
P

total days 01

thanks
 

Attachments

  • Book33.xlsx
    10.7 KB · Views: 6
i want to solve this problem only with formula.
This could be quite a big ask. How easy it is depends on your version of Excel:
(a) what version of Excel are you using?
(b) why must this be formula only?

re:
I also want to calculate holidays only if they fall between either P or OD at both ends.
Not 100% sure what you're asking here but I'll have a stab. I need to be sure I'm getting the logic right.
In picture below, 2 tables. In each the third column tries to convert those holidays to P where those holidays have P or OD at both ends. If I've got this bit right then we can use the third column to do a count.
So have I got this right?
Note that the second table (on the right) has treated OD(TRAINING) on 29th June as plain OD; is this right?
It would have been a good idea not to delete the cells referred to in the data validation.
1693827202995.png
 
Back
Top