Wanting to create a COUNTIFS formula based using an OR function and count number days to due date
Hello, I am trying to create a formula that helps me determine if a submission was created less than 24 hours or 1 day from when the need by date of the completed item not including the weekend. So if a request was submitted Friday and it was Due Monday that still constitutes a less than 1 business day.
I have a helper column already to drop the time from the autopopulated creation date column. so Need by Date and Shortened creation date column formats are the same. I've tried this formula a number of different ways but it doesn't seem to be working so here's the latest version of where I am at.
Any help is greatly appreciated.
Shortened date is the reformatted creation date and NBD is the Need By Date.
=COUNTIFS({Pick Ticket  Shortened Date}, <=NETWORKDAY({Pick Ticket  Shortened Date}, {NorCal Pick System  NBD}))
Second part of this problem is that I want to only count the cells IF the cell is blank "" or <>"Complete"
Best Answer

Hey @Edwin Rosario
I think I understand what you need. You want to count the number of times the NETWORKDAYS is less or equal to one day.
On your crossreferenced sheet, add another Helper column for your NETWORKDAY formula. That way the formula is looking across a single row and comparing the two dates, row by row. As written above, the formula is trying to compare the two entire date columns to one another.
Your COUNTIFS formula would then become
=COUNTIFS({Helper NetWorkDays column},@cell=<1)
Would this work for you?
Kelly
Answers

Hey @Edwin Rosario
I think I understand what you need. You want to count the number of times the NETWORKDAYS is less or equal to one day.
On your crossreferenced sheet, add another Helper column for your NETWORKDAY formula. That way the formula is looking across a single row and comparing the two dates, row by row. As written above, the formula is trying to compare the two entire date columns to one another.
Your COUNTIFS formula would then become
=COUNTIFS({Helper NetWorkDays column},@cell=<1)
Would this work for you?
Kelly

Thanks KDM! That worked perfectly.
Help Article Resources
Categories
Check out the Formula Handbook template!