How to use a formulas to indicate current constellation in sheet summary

freyafan

Hi guys, I have a sheet to indicates constellations by date, and I cannot find a formula to get current constellation based on today's date, can anyone help with this? Much appreciate!

Nick Korna

Hi @freyafan ,

Are your dates displayed as short dates, but are actually full dates like below:

If so, then the following nested IF(AND) formula should work for you:

=IF(AND(TODAY() >= [Start Date]1, TODAY() <= [End Date]1), Constellation1, IF(AND(TODAY() >= [Start Date]2, TODAY() <= [End Date]2), Constellation2, IF(AND(TODAY() >= [Start Date]3, TODAY() <= [End Date]3), Constellation3, IF(AND(TODAY() >= [Start Date]4, TODAY() <= [End Date]4), Constellation4, IF(AND(TODAY() >= [Start Date]5, TODAY() <= [End Date]5), Constellation5, IF(AND(TODAY() >= [Start Date]6, TODAY() <= [End Date]6), Constellation6, IF(AND(TODAY() >= [Start Date]7, TODAY() <= [End Date]7), Constellation7, IF(AND(TODAY() >= [Start Date]8, TODAY() <= [End Date]8), Constellation8, IF(AND(TODAY() >= [Start Date]9, TODAY() <= [End Date]9), Constellation9, IF(AND(TODAY() >= [Start Date]10, TODAY() <= [End Date]10), Constellation10, IF(AND(TODAY() >= [Start Date]11, TODAY() <= [End Date]11), Constellation11, IF(AND(TODAY() >= [Start Date]12, TODAY() <= [End Date]12), Constellation12, ""))))))))))))

Example input as a summary field:

If you wanted to check it for a specific date instead of today, then you can substitute a cell reference for the TODAY() portions of this formula.

This should work up until October 24th next year, by which time you'd need to update the start & end dates.

Hope this helps!

Nick Korna

Hi @freyafan ,

Are your dates displayed as short dates, but are actually full dates like below:

If so, then the following nested IF(AND) formula should work for you:

=IF(AND(TODAY() >= [Start Date]1, TODAY() <= [End Date]1), Constellation1, IF(AND(TODAY() >= [Start Date]2, TODAY() <= [End Date]2), Constellation2, IF(AND(TODAY() >= [Start Date]3, TODAY() <= [End Date]3), Constellation3, IF(AND(TODAY() >= [Start Date]4, TODAY() <= [End Date]4), Constellation4, IF(AND(TODAY() >= [Start Date]5, TODAY() <= [End Date]5), Constellation5, IF(AND(TODAY() >= [Start Date]6, TODAY() <= [End Date]6), Constellation6, IF(AND(TODAY() >= [Start Date]7, TODAY() <= [End Date]7), Constellation7, IF(AND(TODAY() >= [Start Date]8, TODAY() <= [End Date]8), Constellation8, IF(AND(TODAY() >= [Start Date]9, TODAY() <= [End Date]9), Constellation9, IF(AND(TODAY() >= [Start Date]10, TODAY() <= [End Date]10), Constellation10, IF(AND(TODAY() >= [Start Date]11, TODAY() <= [End Date]11), Constellation11, IF(AND(TODAY() >= [Start Date]12, TODAY() <= [End Date]12), Constellation12, ""))))))))))))

Example input as a summary field:

If you wanted to check it for a specific date instead of today, then you can substitute a cell reference for the TODAY() portions of this formula.

This should work up until October 24th next year, by which time you'd need to update the start & end dates.

Hope this helps!