• 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.

Logic Post - related to dates, countif

1shivamkushwaha

New Member
Hi,
This is shivam,

This is a comp off sheet, and i want to automate it.

Comp off sheet is like if i work on holiday , i can avail a leave with in 60days for my comp off date.
1st row: actual date when we have holiday
2nd row: particular information about the holiday
3rd rod: last date to avail the comp off. or expiry date
4th row 2nd col: if (expiry date > today, 0, 1)

Now i want to know how many comp off i can avail today. I DONT WANT KNOW THE TOTAL COMP OFF. I JUST WANT TO GET THE COUNT OF ACTIVE COMP OFF. SO IN THE BELOW CASE EXPECTED OUTPUT SHOULD BE 1.

Explanation: Todays date is 15/07/2021, only this day is following between 5/28/2021 - 7/27/2021.

9/3/2021 is upcoming date so logically, to avail any comp off first i have to work on holiday then only i can take my comp off.



actual date1/1/20215/28/20219/3/2021
EMP IDnew yearabcpqrExpected output
Expiry date3/2/20217/27/202111/2/2021total remaingavail
shivam0112
1
peter01121
anjul0112
1
 

vletm

Excel Ninja
1shivamkushwaha
Please reread Forum Rules
You have shouted = used capital letters.
You wrote that is a comp off -sheet, but You didn't send a sample from it as an Excel-file.
Which format do You use Your dates? ... You've use in two formats.
Is there always those six columns?
 

1shivamkushwaha

New Member
I m new to this group, so I don't know capital letter means shouting. Next time i will keep in mind.
Here is the sheet, and i recheck the date format, but i used the same date format all around the sheet.
date format is mm/dd/yyyy, and coming to column size, you can check the attachment.
 

Attachments

vletm

Excel Ninja
1shivamkushwaha
As You're new in this group
then You've just read those Forum Rules
where has written notes which I gave.

About Dates:
Your Explanation: Todays date is 15/07/2021, only this day is following between 5/28/2021 - 7/27/2021.
... date format is mm/dd/yyyy, in Your file ... it looks for me like below dd/mm/yyyy
Screenshot 2021-07-16 at 08.21.35.png
 

bosco_yip

Excel Ninja
A bit modified your "Result" layout.

In Q2, enter your "Result description", and in Q3 enter your "Criteria date" ( as per below picture)

Then,

In Q4, formula copied down :

=SUMPRODUCT((B4:O4=1)*($B$1:$O$1<=Q$3))

Remark : Please changed my layout date format setting (dd/mm/yyyy) in suit with your own country system (mm/dd/yyyy).

75349
 
Last edited:
Top