I have a table that keeps track of stored items with a date in and date out column.

I've used the formula below to get the total days an item has been stored, which I think works fine (total days is supposed to include the day in and day out), if the date out cell is blank - it is assumed the item is still in storage and gives the total days stored to the current day.

=IF(OR([@[Date In]]="",[@[Date In]] > TODAY()),"",IF([@[Date Out]] = "",TODAY()-[@[Date In]],[@[Date Out]]-[@[Date In]] + 1))

However I need to get the number of days an item was stored in a given month for billing purposes.

I've attempted to create a formula that takes Date In and Date Out column values and returns the total billable days for the month of a date that is entered into cell M2.

Like the total days stored formula, if the Date In column has a date but the Date Out column has no value then it is assumed the item is still currently in storage and either the whole month should be counted (if it was stored before the current month) or the date it was stored up to the end of the month counted. (there's a max 0 in there somewhere because at one point I was getting negative values)

=IF(OR(ISBLANK([@[Date In]]), MONTH([@[Date In]]) > MONTH($M$2)),"",
MAX(0,IF(ISBLANK([@[Date Out]]),IF(MONTH([@[Date In]]) = MONTH($M$2), DAYS(EOMONTH($M$2,0),[@[Date In]]),
IF(MONTH([@[Date In]]) < $M$2, DAY(EOMONTH($M$2,0)), EOMONTH($M$2,0) - DAY([@[Date In]]))),
IF(MONTH([@[Date In]]) = MONTH([@[Date Out]]), [@[Date Out]] - [@[Date In]] + 1, IF([@[Date Out]] > EOMONTH($M$2,0), DAYS(EOMONTH([@[Date In]],0),[@[Date In]]),[@[Date Out]] - $M$2 + 1)))))

Some of the values returned are fine - some are incorrect but I can't understand why.

EG: IN - 01/02/2025 OUT - 05/03/2025 Billing Period - 03/25 return - 5

IN - 02/03/2025 OUT - 05/03/2025 Billing Period - 03/25 return - 4

IN - 02/01/2025 OUT - Billing Period - 03/25 return - 31

IN - 02/02/2025 OUT - 01/04/2025 Billing Period - 03/25 return - 26 (it does this even if OUT is a later month, its like it only counted the days in Feb)

IN - 01/02/2025 OUT - 05/03/2025 Billing Period - 10/25 return - 0

IN - 02/03/2025 OUT - 05/03/2025 Billing Period - 10/25 return - 4 (this should return 0)

I have a table that keeps track of stored items with a date in and date out column.

I've used the formula below to get the total days an item has been stored, which I think works fine (total days is supposed to include the day in and day out), if the date out cell is blank - it is assumed the item is still in storage and gives the total days stored to the current day.

=IF(OR([@[Date In]]="",[@[Date In]] > TODAY()),"",IF([@[Date Out]] = "",TODAY()-[@[Date In]],[@[Date Out]]-[@[Date In]] + 1))

However I need to get the number of days an item was stored in a given month for billing purposes.

I've attempted to create a formula that takes Date In and Date Out column values and returns the total billable days for the month of a date that is entered into cell M2.

Like the total days stored formula, if the Date In column has a date but the Date Out column has no value then it is assumed the item is still currently in storage and either the whole month should be counted (if it was stored before the current month) or the date it was stored up to the end of the month counted. (there's a max 0 in there somewhere because at one point I was getting negative values)

=IF(OR(ISBLANK([@[Date In]]), MONTH([@[Date In]]) > MONTH($M$2)),"",
MAX(0,IF(ISBLANK([@[Date Out]]),IF(MONTH([@[Date In]]) = MONTH($M$2), DAYS(EOMONTH($M$2,0),[@[Date In]]),
IF(MONTH([@[Date In]]) < $M$2, DAY(EOMONTH($M$2,0)), EOMONTH($M$2,0) - DAY([@[Date In]]))),
IF(MONTH([@[Date In]]) = MONTH([@[Date Out]]), [@[Date Out]] - [@[Date In]] + 1, IF([@[Date Out]] > EOMONTH($M$2,0), DAYS(EOMONTH([@[Date In]],0),[@[Date In]]),[@[Date Out]] - $M$2 + 1)))))

Some of the values returned are fine - some are incorrect but I can't understand why.

EG: IN - 01/02/2025 OUT - 05/03/2025 Billing Period - 03/25 return - 5

IN - 02/03/2025 OUT - 05/03/2025 Billing Period - 03/25 return - 4

IN - 02/01/2025 OUT - Billing Period - 03/25 return - 31

IN - 02/02/2025 OUT - 01/04/2025 Billing Period - 03/25 return - 26 (it does this even if OUT is a later month, its like it only counted the days in Feb)

IN - 01/02/2025 OUT - 05/03/2025 Billing Period - 10/25 return - 0

IN - 02/03/2025 OUT - 05/03/2025 Billing Period - 10/25 return - 4 (this should return 0)

• excel
• date
• excel-formula

• 1 One of your examples has an IN that happened later than your OUT? It may be useful to provide an example of how your data is structured so we can get a better idea of how what we're trying to write. A better understanding of what you're trying to accomplish with that table, even if it's manual for now in a small example, I'm not quite following exactly what you're aiming to calculate from the example you provided. I'm not following the relationship between IN-OUT-Return- [your answer] – Mark S. Commented Mar 31 at 20:43
• Is the billing period entered as a date? if so is it the first or the last day of the month? or is it entered as text "02/25"? – tinazmu Commented Mar 31 at 23:06

3 Answers 3

Try:

=LET(RPFIRST, EOMONTH($M$2,-1)+1, RPLAST, EOMONTH(RPFIRST,0), MAX(MIN(RPLAST, IF(B2="",TODAY(), B2))-MAX(RPFIRST,A2)+1,0))

This assumes that $M$2 is a date indicating the reporting month, and the actual reporting month is from the 1st day of the month of this date, and the last day is the last day of that month.

It expects to find the IN date in A2 and the OUT date in B2.

It first evaluates the dates of the first and the last days of the reporting month; then finds how many days of IN-to-OUT period falls into that month.

=LET(first,IF([@[Date In]]="",TODAY(),[@[Date In]]), 
    last,IF([@[Date Out]]="",TODAY(),[@[Date Out]]), 
    billing,$M$2, 
    _SOM, 1+EOMONTH(billing,-1), 
    _EOM, EOMONTH(billing,0), 
    _min, MEDIAN(first, _SOM, last), 
    _max, MEDIAN(first, _EOM, last), 
    IF(OR(_min>_EOM, _max<_SOM, first>last), 0, 1+_max-_min)
)

This grabs the two dates (converting Blanks to Today), and calculates the Start Of Month and End Of Month for the billing date.

Then it uses MEDIAN to get the Start and End dates, using SOM and EOM as bounds.

Finally, it checks if:

• The Bounded Start Date is after the End of the Month (i.e. storage starts after the Billing Month)
• The Bounded End Date is before the Start of the Month (i.e. storage ends before the Billing Month)
• [@[Date In]] is after [@[Date Out]] (which probably indicates an error in Date Entry)

If any of those are true, it returns Zero. Otherwise, it returns the number of days from the Bounded Start to the Bounded End (inclusive)

First day of period: =max([@[Date In]],EoMonth($M$2,-1)+1)

Last day of period: =min(if(Isblank([@[Date Out]]),today(),[@[Date Out]]),EoMonth($M$2,0))

Interval: =1+max(-1,last day - first day)

All together: =1+max(-1,min(if(Isblank([@[Date Out]]),today(),[@[Date Out]]),EoMonth($M$2,0))) - max([@[Date In]],EoMonth($M$2,-1)+1))

Sample demo (I turned all your dates into U.S. dates):