If you want to do this with a formula field, there is a limit to the number of possible dates.
This formula gives you starting point. You can extend it as needed, but as you can see, it can only handle a set maximum number of dates.
REGEX_REPLACE(CONCATENATE(
DATETIME_FORMAT(
{Asset Launch Date},
"M/D/YY"
) & ", ",
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 1,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 1, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 2,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 2, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 3,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 3, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 4,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 4, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 5,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 5, 'day'),
"M/D/YY"
) & ", "
)
), ", $", "")
You could have filter conditions based on “is after” the launch date and “is before” the end date. The user would have to enter dates in both filter conditions, but it is also allows for more flexible searching.
If you want to do this with a formula field, there is a limit to the number of possible dates.
This formula gives you starting point. You can extend it as needed, but as you can see, it can only handle a set maximum number of dates.
REGEX_REPLACE(CONCATENATE(
DATETIME_FORMAT(
{Asset Launch Date},
"M/D/YY"
) & ", ",
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 1,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 1, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 2,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 2, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 3,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 3, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 4,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 4, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 5,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 5, 'day'),
"M/D/YY"
) & ", "
)
), ", $", "")
This is exactly what I was looking for! I really appreciate your help and everything you do for the Airtable community!
You could have filter conditions based on “is after” the launch date and “is before” the end date. The user would have to enter dates in both filter conditions, but it is also allows for more flexible searching.
Tried implementing this (as well as a timeline view), but the individuals who wanted the functionality thought it was “too much work” 
You could have filter conditions based on “is after” the launch date and “is before” the end date. The user would have to enter dates in both filter conditions, but it is also allows for more flexible searching.
I came across this looking for something similar. I am using Airtable to schedule people for different assignments over a period of 4 months. Some people might have the same assignment for the whole time, others will receive many shorter assignments. I am creating an interface for the person who does the scheduling, and want her to be able to use a filter to search for "all people who are unassigned" on a particular day. It seems to me that I would need to use the formula you shared @kuovonne, do a rollup so that all the dates for all assignments would show in one field, and then use the filter in the interface to search for anybody who does not have a particular date in their rollup.
How far can I stretch that formula you shared - meaning how many days can I use it for?
Do you see a better way to achieve what I am trying to do?
Thank you!
I came across this looking for something similar. I am using Airtable to schedule people for different assignments over a period of 4 months. Some people might have the same assignment for the whole time, others will receive many shorter assignments. I am creating an interface for the person who does the scheduling, and want her to be able to use a filter to search for "all people who are unassigned" on a particular day. It seems to me that I would need to use the formula you shared @kuovonne, do a rollup so that all the dates for all assignments would show in one field, and then use the filter in the interface to search for anybody who does not have a particular date in their rollup.
How far can I stretch that formula you shared - meaning how many days can I use it for?
Do you see a better way to achieve what I am trying to do?
Thank you!
I do not think that this formula will work for your use case. As you can see, each additional day requires seven more lines to the formula, so if someone has a single assignment for four months, the formula would need over 840 lines. Even if you could have such a monstrous formula, you would need to do a filtered search for where the formula result does not include the string for the desired date, and the date would need to be typed exactly. And that would only tell you if the person was available that single day, not other days for which the assignment lasts.
In general, Airtable does not handle looking for "unassigned" time slots well without scripting.
If you want to do this with a formula field, there is a limit to the number of possible dates.
This formula gives you starting point. You can extend it as needed, but as you can see, it can only handle a set maximum number of dates.
REGEX_REPLACE(CONCATENATE(
DATETIME_FORMAT(
{Asset Launch Date},
"M/D/YY"
) & ", ",
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 1,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 1, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 2,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 2, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 3,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 3, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 4,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 4, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 5,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 5, 'day'),
"M/D/YY"
) & ", "
)
), ", $", "")
Hello! Would it be possible to create a calendar based on those fields in between? We are trying to a time and attendance and would like to see who is off on certain days. Thank you!
If you want to do this with a formula field, there is a limit to the number of possible dates.
This formula gives you starting point. You can extend it as needed, but as you can see, it can only handle a set maximum number of dates.
REGEX_REPLACE(CONCATENATE(
DATETIME_FORMAT(
{Asset Launch Date},
"M/D/YY"
) & ", ",
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 1,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 1, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 2,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 2, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 3,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 3, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 4,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 4, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 5,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 5, 'day'),
"M/D/YY"
) & ", "
)
), ", $", "")
Brilliant @kuovonne - thank you for doing the leg work.
For anyone else looking for an extension of this formula that would cover a typical vacation of up to 24 days here's the expansion, using fields simply named Start and End:
REGEX_REPLACE(CONCATENATE(
DATETIME_FORMAT(
Start,
"M/D/YY"
) & ", ",
IF(
DATETIME_DIFF(End, Start, 'days') >= 1,
DATETIME_FORMAT(
DATEADD(Start, 1, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 2,
DATETIME_FORMAT(
DATEADD(Start, 2, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 3,
DATETIME_FORMAT(
DATEADD(Start, 3, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 4,
DATETIME_FORMAT(
DATEADD(Start, 4, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 5,
DATETIME_FORMAT(
DATEADD(Start, 5, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 6,
DATETIME_FORMAT(
DATEADD(Start, 6, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 7,
DATETIME_FORMAT(
DATEADD(Start, 7, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 8,
DATETIME_FORMAT(
DATEADD(Start, 8, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 9,
DATETIME_FORMAT(
DATEADD(Start, 9, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 10,
DATETIME_FORMAT(
DATEADD(Start, 10, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 11,
DATETIME_FORMAT(
DATEADD(Start, 11, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 12,
DATETIME_FORMAT(
DATEADD(Start, 12, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 13,
DATETIME_FORMAT(
DATEADD(Start, 13, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 14,
DATETIME_FORMAT(
DATEADD(Start, 14, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 15,
DATETIME_FORMAT(
DATEADD(Start, 15, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 16,
DATETIME_FORMAT(
DATEADD(Start, 16, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 17,
DATETIME_FORMAT(
DATEADD(Start, 17, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 18,
DATETIME_FORMAT(
DATEADD(Start, 18, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 19,
DATETIME_FORMAT(
DATEADD(Start, 19, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 20,
DATETIME_FORMAT(
DATEADD(Start, 20, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 21,
DATETIME_FORMAT(
DATEADD(Start, 21, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 22,
DATETIME_FORMAT(
DATEADD(Start, 22, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 23,
DATETIME_FORMAT(
DATEADD(Start, 23, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF(End, Start, 'days') >= 24,
DATETIME_FORMAT(
DATEADD(Start, 24, 'day'),
"M/D/YY"
) & ", "
)
), ", $", "")
If you want to do this with a formula field, there is a limit to the number of possible dates.
This formula gives you starting point. You can extend it as needed, but as you can see, it can only handle a set maximum number of dates.
REGEX_REPLACE(CONCATENATE(
DATETIME_FORMAT(
{Asset Launch Date},
"M/D/YY"
) & ", ",
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 1,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 1, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 2,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 2, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 3,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 3, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 4,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 4, 'day'),
"M/D/YY"
) & ", "
),
IF(
DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 5,
DATETIME_FORMAT(
DATEADD({Asset Launch Date}, 5, 'day'),
"M/D/YY"
) & ", "
)
), ", $", "")
This was wonderfully helpful to me.
What results on my end is a string of dates, listed in a single field.
Is there a way within airtable to translate the results of this formula into individual date fields?
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')
), ''))
This was wonderfully helpful to me.
What results on my end is a string of dates, listed in a single field.
Is there a way within airtable to translate the results of this formula into individual date fields?
You can save it to csv file and then upload with 'use first row as headers' setting disabled
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))
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!
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!
I think my explanation was wrong. The formula works well for any year of start/end, as long as they are the same year. If you set end date on the next year from start, it displays dates only till the end of year.
If you need dates from next year, you can try to add &(IF(YEAR(end)=YEAR(start)+1, ... the same formula, adjusted for end.)
Actually the formula displays full list of year dates, 31 day * 12 months, adjusts months duration, adjust 29.02 when YEAR/4 is integer (therefore it doesn't work correctly for the year 2100, which is not a leap year 🙂 ). Finally, it cuts dates from beginning according to start and cuts dates later than end. LEFT(REPLACE( & 2 last lines
Version more convenient to edit
IF(AND(start,end),LEFT(
REPLACE(
SUBSTITUTE(
REGEX_REPLACE(
REGEX_REPLACE('M01MM02MM03MM04MM05MM06MM07MM08MM09MM10MM11MM12M','M(\\d\\d)M', SUBSTITUTE(
'd01d02d03d04d05d06d07d08d09d10d11d12d13d14d15d16d17d18d19d20d21d22d23d24d25d26d27d28d29d30d31'
,'d', ', ' &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*VALUE(DATETIME_FORMAT(start,'DDD'))-10,''),
12*DATETIME_DIFF(end,start,'days')+10))
The most interesting thing here - using regex_replace to make a kind of loop in formula
Note: sometimes, when copied from a cell (for example I store my scripts and formulas in a dedicated table), the formula can shift few days back for dates starting from March and later. That's because when copying date from older tables, space copied as 'another space'(used to prevent line break in it) with code 120 instead of 32. To fix you should review formula in the line with two '-02-29, ' and the line above, with regex used to remove 31st from months with less than 31 days, and put the correct spaces after comma manually.
I think my explanation was wrong. The formula works well for any year of start/end, as long as they are the same year. If you set end date on the next year from start, it displays dates only till the end of year.
If you need dates from next year, you can try to add &(IF(YEAR(end)=YEAR(start)+1, ... the same formula, adjusted for end.)
Actually the formula displays full list of year dates, 31 day * 12 months, adjusts months duration, adjust 29.02 when YEAR/4 is integer (therefore it doesn't work correctly for the year 2100, which is not a leap year 🙂 ). Finally, it cuts dates from beginning according to start and cuts dates later than end. LEFT(REPLACE( & 2 last lines
Version more convenient to edit
IF(AND(start,end),LEFT(
REPLACE(
SUBSTITUTE(
REGEX_REPLACE(
REGEX_REPLACE('M01MM02MM03MM04MM05MM06MM07MM08MM09MM10MM11MM12M','M(\\d\\d)M', SUBSTITUTE(
'd01d02d03d04d05d06d07d08d09d10d11d12d13d14d15d16d17d18d19d20d21d22d23d24d25d26d27d28d29d30d31'
,'d', ', ' &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*VALUE(DATETIME_FORMAT(start,'DDD'))-10,''),
12*DATETIME_DIFF(end,start,'days')+10))
The most interesting thing here - using regex_replace to make a kind of loop in formula
Note: sometimes, when copied from a cell (for example I store my scripts and formulas in a dedicated table), the formula can shift few days back for dates starting from March and later. That's because when copying date from older tables, space copied as 'another space'(used to prevent line break in it) with code 120 instead of 32. To fix you should review formula in the line with two '-02-29, ' and the line above, with regex used to remove 31st from months with less than 31 days, and put the correct spaces after comma manually.
Thanks a lot Alexey! That works perfect for my needs and the new formula is also easier to edit and understand. Quite cool that if works with the loops like this!
Much appreciated 🙂
