Wanting to create a COUNTIFS formula based using an OR function and count number days to due date

Edwin Rosario

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 auto-populated 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"

Kelly Moore

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 cross-referenced 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

Kelly Moore

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 cross-referenced 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

Edwin Rosario

Thanks KDM! That worked perfectly.