Container Warehouse Storage

[TripleJays]

Hi all,

I need to calculate storage days for containers being stored in our depot. We bill weekly so if a container is still in storage for the full week of the billing period, I would need a result of 7 days and if it was collected during the week then I need the partial days automatically calculated. If the container was collected prior to the billing period, I need a result of 0.

In this example
* Row16 - correct
* Row17- Should be 7 and then 3 in column x (=10 days)
* Row18 - Should be 2 days

Date in / date out = 1 day

I am using =IF(E16>0,E16-$Y$14 + 1,$Y$15-$Y$14+1). I have spent hours googling, trying to work this out myself and would greatly appreciate any help.

I also have customers that have a number of free days that would be in column c, so if anyone could help including this in the formula too.

Thanks

 

[jdellasala]

There is a nearly undocumented function called DATEDIF that's an old holdover from Lotus 123 compatibility that might be of help.

Book1
	A	B	C
1	12/01/2022	12/07/2022	6
Sheet1

Cell Formulas
Range	Formula
C1	C1	=DATEDIF(A1,B1,"d")

When you type "datedif" you can't even hit [Tab] to start the function - it has to be typed, however once the open parentheses is there, it does appear and the name can be clicked for help

Complete help will appear when you click on DATEDIF, and can also be found here.

I don't understand why the function isn't officially supported. Granted, you can get the same answer by doing simple subtraction, but to get months or years gets tricky, and sometimes it just fits better.