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

  • Comp off- logic.xlsx
    12.9 KB · Views: 3
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
 
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:
Back
Top