Re: Formula listing ALL dates between between {Start Date} field & {End Date} field

Nick_Robeson · ‎Aug 26, 2022

Hi all,

Hoping someone can help with a solution! My company has numerous assets that are “active” for long periods of time. I have a formula to calculate the “Active Status” based on the Asset Launch Date & Asset End Date (see below for reference).

I would like to make an additional field that lists ALL dates an asset is going to be active based on the start and end dates (placeholder included in the image above). For example, if Asset Launch Date = 9/1/22 & Asset End Date = 9/5/22, I’d like the ALL ACTIVE DATES field to read: “9/1/22, 9/2/22, 9/3/22, 9/4/22, 9/5/22”

Note: This field would be hidden 99.9% of the time and ONLY used in an Interface Filter element to allow users to type in specific dates (i.e. 9/3/22) and see the assets that will be “Active” on that date.

If there is another solution you can think of, feel free to leave it here as well!

Thank you in advance!

Alexey_Gusev · ‎Oct 19, 2024

Hi,
I followed another way, it works from {start} to {end} of the current year

IF(AND(start,end), REPLACE(  LEFT(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('g1f1, g1f2, g1f3, g1f4, g1f5, g1f6, g1f7, g1f8, g1f9, g1b0, g1b1, g1b2, g1b3, g1b4, g1b5, g1b6, g1b7, g1b8, g1b9, g1c0, g1c1, g1c2, g1c3, g1c4, g1c5, g1c6, g1c7, g1c8, g1c9, g1-30, g1-31, g2f1, g2f2, g2f3, g2f4, g2f5, g2f6, g2f7, g2f8, g2f9, g2b0, g2b1, g2b2, g2b3, g2b4, g2b5, g2b6, g2b7, g2b8, g2b9, g2c0, g2c1, g2c2, g2c3, g2c4, g2c5, g2c6, g2c7, g2c8, g3f1, g3f2, g3f3, g3f4, g3f5, g3f6, g3f7, g3f8, g3f9, g3b0, g3b1, g3b2, g3b3, g3b4, g3b5, g3b6, g3b7, g3b8, g3b9, g3c0, g3c1, g3c2, g3c3, g3c4, g3c5, g3c6, g3c7, g3c8, g3c9, g3-30, g3-31, g4f1, g4f2, g4f3, g4f4, g4f5, g4f6, g4f7, g4f8, g4f9, g4b0, g4b1, g4b2, g4b3, g4b4, g4b5, g4b6, g4b7, g4b8, g4b9, g4c0, g4c1, g4c2, g4c3, g4c4, g4c5, g4c6, g4c7, g4c8, g4c9, g4-30, g5f1, g5f2, g5f3, g5f4, g5f5, g5f6, g5f7, g5f8, g5f9, g5b0, g5b1, g5b2, g5b3, g5b4, g5b5, g5b6, g5b7, g5b8, g5b9, g5c0, g5c1, g5c2, g5c3, g5c4, g5c5, g5c6, g5c7, g5c8, g5c9, g5-30, g5-31, g6f1, g6f2, g6f3, g6f4, g6f5, g6f6, g6f7, g6f8, g6f9, g6b0, g6b1, g6b2, g6b3, g6b4, g6b5, g6b6, g6b7, g6b8, g6b9, g6c0, g6c1, g6c2, g6c3, g6c4, g6c5, g6c6, g6c7, g6c8, g6c9, g6-30, g7f1, g7f2, g7f3, g7f4, g7f5, g7f6, g7f7, g7f8, g7f9, g7b0, g7b1, g7b2, g7b3, g7b4, g7b5, g7b6, g7b7, g7b8, g7b9, g7c0, g7c1, g7c2, g7c3, g7c4, g7c5, g7c6, g7c7, g7c8, g7c9, g7-30, g7-31, g8f1, g8f2, g8f3, g8f4, g8f5, g8f6, g8f7, g8f8, g8f9, g8b0, g8b1, g8b2, g8b3, g8b4, g8b5, g8b6, g8b7, g8b8, g8b9, g8c0, g8c1, g8c2, g8c3, g8c4, g8c5, g8c6, g8c7, g8c8, g8c9, g8-30, g8-31, g9f1, g9f2, g9f3, g9f4, g9f5, g9f6, g9f7, g9f8, g9f9, g9b0, g9b1, g9b2, g9b3, g9b4, g9b5, g9b6, g9b7, g9b8, g9b9, g9c0, g9c1, g9c2, g9c3, g9c4, g9c5, g9c6, g9c7, g9c8, g9c9, g9-30, i0f1, i0f2, i0f3, i0f4, i0f5, i0f6, i0f7, i0f8, i0f9, i0b0, i0b1, i0b2, i0b3, i0b4, i0b5, i0b6, i0b7, i0b8, i0b9, i0c0, i0c1, i0c2, i0c3, i0c4, i0c5, i0c6, i0c7, i0c8, i0c9, i0-30, i0-31, i1f1, i1f2, i1f3, i1f4, i1f5, i1f6, i1f7, i1f8, i1f9, i1b0, i1b1, i1b2, i1b3, i1b4, i1b5, i1b6, i1b7, i1b8, i1b9, i1c0, i1c1, i1c2, i1c3, i1c4, i1c5, i1c6, i1c7, i1c8, i1c9, i1-30, i2f1, i2f2, i2f3, i2f4, i2f5, i2f6, i2f7, i2f8, i2f9, i2b0, i2b1, i2b2, i2b3, i2b4, i2b5, i2b6, i2b7, i2b8, i2b9, i2c0, i2c1, i2c2, i2c3, i2c4, i2c5, i2c6, i2c7, i2c8, i2c9, i2-30, i2-31', 'g', YEAR(TODAY())&'-0'), 'i', YEAR(TODAY())&'-1'), 'f', '-0'), 'b', '-1'), 'c', '-2'), 9+FIND(DATESTR(end), 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('g1f1, g1f2, g1f3, g1f4, g1f5, g1f6, g1f7, g1f8, g1f9, g1b0, g1b1, g1b2, g1b3, g1b4, g1b5, g1b6, g1b7, g1b8, g1b9, g1c0, g1c1, g1c2, g1c3, g1c4, g1c5, g1c6, g1c7, g1c8, g1c9, g1-30, g1-31, g2f1, g2f2, g2f3, g2f4, g2f5, g2f6, g2f7, g2f8, g2f9, g2b0, g2b1, g2b2, g2b3, g2b4, g2b5, g2b6, g2b7, g2b8, g2b9, g2c0, g2c1, g2c2, g2c3, g2c4, g2c5, g2c6, g2c7, g2c8, g3f1, g3f2, g3f3, g3f4, g3f5, g3f6, g3f7, g3f8, g3f9, g3b0, g3b1, g3b2, g3b3, g3b4, g3b5, g3b6, g3b7, g3b8, g3b9, g3c0, g3c1, g3c2, g3c3, g3c4, g3c5, g3c6, g3c7, g3c8, g3c9, g3-30, g3-31, g4f1, g4f2, g4f3, g4f4, g4f5, g4f6, g4f7, g4f8, g4f9, g4b0, g4b1, g4b2, g4b3, g4b4, g4b5, g4b6, g4b7, g4b8, g4b9, g4c0, g4c1, g4c2, g4c3, g4c4, g4c5, g4c6, g4c7, g4c8, g4c9, g4-30, g5f1, g5f2, g5f3, g5f4, g5f5, g5f6, g5f7, g5f8, g5f9, g5b0, g5b1, g5b2, g5b3, g5b4, g5b5, g5b6, g5b7, g5b8, g5b9, g5c0, g5c1, g5c2, g5c3, g5c4, g5c5, g5c6, g5c7, g5c8, g5c9, g5-30, g5-31, g6f1, g6f2, g6f3, g6f4, g6f5, g6f6, g6f7, g6f8, g6f9, g6b0, g6b1, g6b2, g6b3, g6b4, g6b5, g6b6, g6b7, g6b8, g6b9, g6c0, g6c1, g6c2, g6c3, g6c4, g6c5, g6c6, g6c7, g6c8, g6c9, g6-30, g7f1, g7f2, g7f3, g7f4, g7f5, g7f6, g7f7, g7f8, g7f9, g7b0, g7b1, g7b2, g7b3, g7b4, g7b5, g7b6, g7b7, g7b8, g7b9, g7c0, g7c1, g7c2, g7c3, g7c4, g7c5, g7c6, g7c7, g7c8, g7c9, g7-30, g7-31, g8f1, g8f2, g8f3, g8f4, g8f5, g8f6, g8f7, g8f8, g8f9, g8b0, g8b1, g8b2, g8b3, g8b4, g8b5, g8b6, g8b7, g8b8, g8b9, g8c0, g8c1, g8c2, g8c3, g8c4, g8c5, g8c6, g8c7, g8c8, g8c9, g8-30, g8-31, g9f1, g9f2, g9f3, g9f4, g9f5, g9f6, g9f7, g9f8, g9f9, g9b0, g9b1, g9b2, g9b3, g9b4, g9b5, g9b6, g9b7, g9b8, g9b9, g9c0, g9c1, g9c2, g9c3, g9c4, g9c5, g9c6, g9c7, g9c8, g9c9, g9-30, i0f1, i0f2, i0f3, i0f4, i0f5, i0f6, i0f7, i0f8, i0f9, i0b0, i0b1, i0b2, i0b3, i0b4, i0b5, i0b6, i0b7, i0b8, i0b9, i0c0, i0c1, i0c2, i0c3, i0c4, i0c5, i0c6, i0c7, i0c8, i0c9, i0-30, i0-31, i1f1, i1f2, i1f3, i1f4, i1f5, i1f6, i1f7, i1f8, i1f9, i1b0, i1b1, i1b2, i1b3, i1b4, i1b5, i1b6, i1b7, i1b8, i1b9, i1c0, i1c1, i1c2, i1c3, i1c4, i1c5, i1c6, i1c7, i1c8, i1c9, i1-30, i2f1, i2f2, i2f3, i2f4, i2f5, i2f6, i2f7, i2f8, i2f9, i2b0, i2b1, i2b2, i2b3, i2b4, i2b5, i2b6, i2b7, i2b8, i2b9, i2c0, i2c1, i2c2, i2c3, i2c4, i2c5, i2c6, i2c7, i2c8, i2c9, i2-30, i2-31', 'g', YEAR(TODAY())&'-0'), 'i', YEAR(TODAY())&'-1'), 'f', '-0'), 'b', '-1'), 'c', '-2')
)), 1, -1+FIND(DATESTR(start), 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('g1f1, g1f2, g1f3, g1f4, g1f5, g1f6, g1f7, g1f8, g1f9, g1b0, g1b1, g1b2, g1b3, g1b4, g1b5, g1b6, g1b7, g1b8, g1b9, g1c0, g1c1, g1c2, g1c3, g1c4, g1c5, g1c6, g1c7, g1c8, g1c9, g1-30, g1-31, g2f1, g2f2, g2f3, g2f4, g2f5, g2f6, g2f7, g2f8, g2f9, g2b0, g2b1, g2b2, g2b3, g2b4, g2b5, g2b6, g2b7, g2b8, g2b9, g2c0, g2c1, g2c2, g2c3, g2c4, g2c5, g2c6, g2c7, g2c8, g3f1, g3f2, g3f3, g3f4, g3f5, g3f6, g3f7, g3f8, g3f9, g3b0, g3b1, g3b2, g3b3, g3b4, g3b5, g3b6, g3b7, g3b8, g3b9, g3c0, g3c1, g3c2, g3c3, g3c4, g3c5, g3c6, g3c7, g3c8, g3c9, g3-30, g3-31, g4f1, g4f2, g4f3, g4f4, g4f5, g4f6, g4f7, g4f8, g4f9, g4b0, g4b1, g4b2, g4b3, g4b4, g4b5, g4b6, g4b7, g4b8, g4b9, g4c0, g4c1, g4c2, g4c3, g4c4, g4c5, g4c6, g4c7, g4c8, g4c9, g4-30, g5f1, g5f2, g5f3, g5f4, g5f5, g5f6, g5f7, g5f8, g5f9, g5b0, g5b1, g5b2, g5b3, g5b4, g5b5, g5b6, g5b7, g5b8, g5b9, g5c0, g5c1, g5c2, g5c3, g5c4, g5c5, g5c6, g5c7, g5c8, g5c9, g5-30, g5-31, g6f1, g6f2, g6f3, g6f4, g6f5, g6f6, g6f7, g6f8, g6f9, g6b0, g6b1, g6b2, g6b3, g6b4, g6b5, g6b6, g6b7, g6b8, g6b9, g6c0, g6c1, g6c2, g6c3, g6c4, g6c5, g6c6, g6c7, g6c8, g6c9, g6-30, g7f1, g7f2, g7f3, g7f4, g7f5, g7f6, g7f7, g7f8, g7f9, g7b0, g7b1, g7b2, g7b3, g7b4, g7b5, g7b6, g7b7, g7b8, g7b9, g7c0, g7c1, g7c2, g7c3, g7c4, g7c5, g7c6, g7c7, g7c8, g7c9, g7-30, g7-31, g8f1, g8f2, g8f3, g8f4, g8f5, g8f6, g8f7, g8f8, g8f9, g8b0, g8b1, g8b2, g8b3, g8b4, g8b5, g8b6, g8b7, g8b8, g8b9, g8c0, g8c1, g8c2, g8c3, g8c4, g8c5, g8c6, g8c7, g8c8, g8c9, g8-30, g8-31, g9f1, g9f2, g9f3, g9f4, g9f5, g9f6, g9f7, g9f8, g9f9, g9b0, g9b1, g9b2, g9b3, g9b4, g9b5, g9b6, g9b7, g9b8, g9b9, g9c0, g9c1, g9c2, g9c3, g9c4, g9c5, g9c6, g9c7, g9c8, g9c9, g9-30, i0f1, i0f2, i0f3, i0f4, i0f5, i0f6, i0f7, i0f8, i0f9, i0b0, i0b1, i0b2, i0b3, i0b4, i0b5, i0b6, i0b7, i0b8, i0b9, i0c0, i0c1, i0c2, i0c3, i0c4, i0c5, i0c6, i0c7, i0c8, i0c9, i0-30, i0-31, i1f1, i1f2, i1f3, i1f4, i1f5, i1f6, i1f7, i1f8, i1f9, i1b0, i1b1, i1b2, i1b3, i1b4, i1b5, i1b6, i1b7, i1b8, i1b9, i1c0, i1c1, i1c2, i1c3, i1c4, i1c5, i1c6, i1c7, i1c8, i1c9, i1-30, i2f1, i2f2, i2f3, i2f4, i2f5, i2f6, i2f7, i2f8, i2f9, i2b0, i2b1, i2b2, i2b3, i2b4, i2b5, i2b6, i2b7, i2b8, i2b9, i2c0, i2c1, i2c2, i2c3, i2c4, i2c5, i2c6, i2c7, i2c8, i2c9, i2-30, i2-31', 'g', YEAR(TODAY())&'-0'), 'i', YEAR(TODAY())&'-1'), 'f', '-0'), 'b', '-1'), 'c', '-2')
), ''))

Alexey_Gusev · ‎Oct 19, 2024

You can save it to csv file and then upload with 'use first row as headers' setting disabled

Alexey_Gusev · ‎Nov 09, 2024

btw, for anybody interested, I have found how to do a kind of nested loop with regex, so it can be done without huge formula. only current year dates (year of {start}) displayed.

IF(AND(start,end),LEFT( REPLACE(SUBSTITUTE(SUBSTITUTE(REGEX_REPLACE(
REGEX_REPLACE('w01w w02w w03w w04w w05w w06w w07w w08w w09w w10w w11w w12w','w(\\d\\d)w',SUBSTITUTE('x01x02x03x04x05x06x07x08x09x10x11x12x13x14x15x16x17x18x19x20x21x22x23x24x25x26x27x28x29x30x31, ','x', ', '&YEAR(start)&'-'&RIGHT('0'&'$1',2)&'-')),YEAR(start)&'-((0[2469]|11)-31|02-30), ',''),YEAR(start)&'-02-29, ',IF(MOD(YEAR(start),4),'',YEAR(start)&'-02-29, ')),',  , ',', '),1,12*DATETIME_DIFF(start,YEAR(TODAY())&'-01-01','days')+2,''),12*DATETIME_DIFF(end,start,'days')+10))

AndreasG · ‎Nov 27, 2024

thanks a lot @Alexey_Gusev your formula works like a charm!

But is there any option to make it work for the next year as well?

Cheers!