How do I write this formula?

mrios

I want to write a formula that calculates the number of days we have from today until we run out of stock.

I have:

The number of Laptops Available
The Avg Monthly Hires

and

I know we give out laptops at NEO every other Monday (Next one in February 17, then March 3rd, March 17th so on)

How can we tell the number of days until we wont have enough laptops in stock and make it accurate by day. Both the number of [Laptops Available and the [Avg Monthly Hires] are dynamic, but the frequency of laptop distribution will essentially stay the same.

The closest I can get is this:

=IF([Avg Monthly Hires]@row = 0, "N/A", IF([Laptops Available]@row = 0, 0, IF(TODAY() <= DATE(2025, 2, 17), IF([Laptops Available]@row <= ([Avg Monthly Hires]@row / 2), DATE(2025, 2, 17) - TODAY(), IF([Laptops Available]@row <= ([Avg Monthly Hires]@row), (DATE(2025, 2, 17) - TODAY()) + 14, (DATE(2025, 2, 17) - TODAY()) + (14 * (CEILING(([Laptops Available]@row) / ([Avg Monthly Hires]@row / 2), 1) - 1)))), IF([Laptops Available]@row <= ([Avg Monthly Hires]@row / 2), (14 - MOD(TODAY() - DATE(2025, 2, 17), 14)), IF([Laptops Available]@row <= ([Avg Monthly Hires]@row), (14 - MOD(TODAY() - DATE(2025, 2, 17), 14)) + 14, (14 - MOD(TODAY() - DATE(2025, 2, 17), 14)) + (14 * (CEILING(([Laptops Available]@row) / ([Avg Monthly Hires]@row / 2), 1) - 1)))))))

Example:
If we have 8 Laptops Available and we hire 6 people on average a month then at the next session we should expect to distribute 3, have 5 remaining, distribute 3 at the following session, and have 2 remaining. That means we could only support 2 sessions - making day 0 March 3rd, which is 22 days from today (February 9th). Any advice?

mrios

I have also tried this formula, but it still seems to come up short

=IF([Laptops Available]@row <= 0, 0, (FLOOR([Laptops Available]@row / ([Avg Monthly Hires]@row / 2), 1) * 14) - MOD(DATE(2025, 2, 17) - TODAY(), 14))

Isaac A.

Hi @mrios!

After trying to test and find a solution for you, we might need more details about your setup to determine the best approach.

Could you share a screenshot of your sheet showing the structure and details? Please make sure to blur any sensitive information or provide a sheet with mock data that follows the same structure.

Additionally, can you provide some example values along with the expected outcome? This way, we can ensure that we’re getting the right results when testing.

Looking forward to your response!

Cheers,
Isaac.