Forum Discussion
Solved
Storage required area within a project Period
Hi all, I'm trying to calculate the peak required area for storage when i have the dates of all materials entry and exit dates. Can excel help calculating the peak required area after entering area...
Does this do what you want?
Your request is a bit vague. In the following, I have assumed that you want to count the maximum numbr of items stored on a single day.
Let's say the date of entry is in B2:B51 and the date of exit in C2:C51.
The maximum is returned by
=LET(
Date_In, B2:B51,
Date_Out, C2:C51,
First_Date, MIN(Date_In),
Last_Date, MAX(Date_Out),
Number_of_Dates, Last_Date-First_Date+1,
Dates, SEQUENCE(Number_of_Dates, 1, First_Date),
Used, COUNTIFS(Date_In,"<="&Dates,Date_Out,">="&Dates),
MAX(Used)
)
thank you for your reply, but how can i translate the above to excel?
It is an Excel formula; it works in Excel in Microsoft 365, Office 2021 and on the web (in the browser).
- i really appreciate it. is it possible also to reflect that in a chart showing materials in and out through indicated time?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?