Formula to check the Unit availability

Joseph Aloysias

I would like to check the Unit availability, we want to check the start date & end date and U1, U2, U3, etc. and unit availability column should provide whether it's available/nonavailable to use for other projects

Is there any formula that can do this?

Also when a unit is assigned for a particular project for a particular duration it shouldn't be available for other projects

Paul Newcome

You would add it inside of the COUNTIFS as a range criteria set similar to the Unit range/criteria.

COUNTIFS([Project Status]:[Project Status], @cell = "Completed", ..............................

Paul Newcome

Are you looking to compare it to "Today" or just compare it to the rest of the entries in the sheet for overlap?

Joseph Aloysias

Trying to compare with all entries for overlap, for example, the start date is 1/11/2022 and end date is 22/11/2022, and Unit 1 booked for the particular date range it should show "not available" when other pm's are selecting unit1

It should check the start date, end date and "unit" column to validate the availability of a particular unit

Paul Newcome

In that case you would compare [Start Date]@row to the End Date column and [End Date]@row to the Start Date column (along with unit number of course) like so:

=IF(COUNTIFS(Unit:Unit, @cell = Unit@row, [Start Date]:[Start Date], @cell<= [End Date]@row, [End Date]:[End Date], @cell>= [Start Date]@row) = 1, "Available", "Not Available")

Joseph Aloysias

Thank you Paul, it's working. If we need to add based on the project status (Completed, Not Started, In-progress). How can we add it.

I tried doing it but it was showing #UNPARSEABLE error message

=IF(COUNTIFS(Unit:Unit, @cell = Unit@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) = 1,[project status]@row="completed" "Available", "Not Available")

Paul Newcome

You would add it inside of the COUNTIFS as a range criteria set similar to the Unit range/criteria.

COUNTIFS([Project Status]:[Project Status], @cell = "Completed", ..............................

Joseph Aloysias

@Paul Newcome thank you, it's working now.

Paul Newcome

Happy to help. 👍️

Joseph Aloysias

@Paul Newcome Can you please help me with the complete formula,

if the unit is occupied for a particular date range(actual start date and Actual end date) and "project status:in progress" it should show not available, and if the project status is completed then it should show available.

=IF(COUNTIFS([Manufacturing Unit]:[Manufacturing Unit], @cell = [Manufacturing Unit]@row, [Actual Start Date]:[Actual Start Date], @cell <= [Actual End Date]@row, [Actual End Date]:[Actual End Date], @cell >= [Actual Start Date]@row, [Project Status]:[Project Status], @cell = "In Progress") = 1, "Available", "Not Available")

Paul Newcome

@Domnic Victor It looks like it is working in your screenshot.

Joseph Aloysias

@Paul Newcome In the fourth row project status is "in progress" then it should show as "not available" but showing it as "available"

Paul Newcome

The other entry for that unit does have a date overlap, but it is marked as Completed. So you only have 1 entry for Unit 2 for those dates that is In Progress.