formulas are missing last day of the months

OshaK

Hello,

I have formulas that bundle dates together for a custom display our team needs and found a glitch. The formulas calculate based on a row number, but then are missing the last day of the month regardless if the month has 31 or 29 days in it.

We need to display dates like in 'Days off' and 'All Dates Off''.

Adjacent column (not visible for users - it gets checked off if the column 'Days Away' gets checked) has:

=AND([Days Away]@row = 1, OR(IFERROR(INDEX([Days Away]$3:[Days Away]$33, MATCH([Date Only]@row + 1, [Date Only]$3:[Date Only]$33, 0)), 0) = 1, IFERROR(INDEX([Days Away]$3:[Days Away]$33, MATCH([Date Only]@row - 1, [Date Only]$3:[Date Only]$33, 0)), 0) = 1))

Helper column (not visible for users - bundles adjacent dates) has:

=IF(AND([Days Away]@row = 1, Adjacent@row = 1), (MAX(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, <[Date Only]@row, Adjacent$3:Adjacent$33, 0)) + 1) + "-" + (MIN(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, >[Date Only]@row, Adjacent$3:Adjacent$33, 0)) - 1), IF([Days Away]@row = 1, [Date Only]@row)) + ""

Dates off column ( visible for users - has a formula only in row 3 and is visible in user output) has:

="January" + " " + JOIN(DISTINCT(COLLECT(Helper3:Helper33, Helper3:Helper33, <>"")), ",")

All Dates Off column (visible for users - has formula only in row 3 and is visible in user output) has:

=JOIN(DISTINCT(COLLECT([Dates off]$3:[Dates off]$33, [Dates off]$3:[Dates off]$33, <>"")), CHAR(10))

If Dates Away is not checked on January 31, everything looks fine:

but as soon as I check the '31', it looks like that;

It happens in consecutive months (for example, for February 2024, checking day 29 causes error.

in march - 31,

in april - 30

and so on.

I change the row number for each month in the formulas, so there is an error in the first formula.

Please help!

DKazatsky2

Hi @OshaK,

Give this version a try - in the Helper column.

=IF(AND([Days Away]@row = 1, Adjacent@row = 1), (MAX(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, <[Date Only]@row, Adjacent$3:Adjacent$33, 0)) + 1) + "-" + IF(MAX(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, >[Date Only]@row, Adjacent$3:Adjacent$33, 0)) = 0, (MAX(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, >=[Date Only]@row, Adjacent$3:Adjacent$33, 1))), MIN(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, >[Date Only]@row, Adjacent$3:Adjacent$33, 0)) - 1), IF([Days Away]@row = 1, [Date Only]@row)) + ""

Hope this helps,

Dave

DKazatsky2

Hi @OshaK,

Give this version a try - in the Helper column.

=IF(AND([Days Away]@row = 1, Adjacent@row = 1), (MAX(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, <[Date Only]@row, Adjacent$3:Adjacent$33, 0)) + 1) + "-" + IF(MAX(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, >[Date Only]@row, Adjacent$3:Adjacent$33, 0)) = 0, (MAX(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, >=[Date Only]@row, Adjacent$3:Adjacent$33, 1))), MIN(COLLECT([Date Only]$3:[Date Only]$33, [Date Only]$3:[Date Only]$33, >[Date Only]@row, Adjacent$3:Adjacent$33, 0)) - 1), IF([Days Away]@row = 1, [Date Only]@row)) + ""

Hope this helps,

Dave

OshaK

@DKazatsky2 that works beautifully, thank you so much! 🙏🏻