In need of human brain to resolve AI formula.

I have a text column that says the following: 09/2022

I would like to turn this into a date for the last day of the month, like this: 09/30/2022

This is what the AI generated formula gave me:

=DATE(YEAR(VALUE([Delayed Result Date]@row)), MONTH(VALUE([Delayed Result Date]@row)) + 1, 0)

I'm getting a #INVALID VALUE error

The column I'm attempting to populate is a date column.


I DO have a formula that will turn the 09/2022 into 09/20/22 (I'll paste it below), *which is close*, but I really need it to be the last day of the month as this is a legal due date I'm tracking.

=IFERROR(DATE(VALUE(RIGHT([Delayed Result Date]@row, 4)), VALUE(LEFT([Delayed Result Date]@row, 2)), VALUE(MID([Delayed Result Date]@row, FIND("/", [Delayed Result Date]@row) + 1, 2))), [Delayed Result Date]@row)


Can anyone either assist me with the AI generated formula or tweak the formula that is about 10 days off?


Thank you!

Meredith

Meredith Rhodes, PhD

ClinicalTrials.gov Specialist

UW School of Medicine & Public Health

UW Clinical Trials Institute

mkrhodes@clinicaltrials.wisc.edu

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @Meredith Rhodes

    Your date in the Delayed Result Date is not a real date - it is a textstring that looks like a date.

    Change your current formula in the Delay Type to this:

    =DATE(VALUE(RIGHT([Delay Type]@row, 4)), VALUE(MID([Delay Type]@row, FIND("/", [Delay Type]@row) - 2, 2)), 1)

    This should fix the other column.

    Kelly

«1

Answers

  • Razetto
    Razetto ✭✭✭✭✭✭

    @Meredith Rhodes Hi, I have a way of doing it but it's a long formula; I'm sure someone will know of an easier one. For example, the formula below is for Sept thru Nov:

    =IF(VALUE(LEFT([Delayed Result Date]@row, 2)) = "9", "09/30" + "/" + VALUE(RIGHT([Delayed Result Date]@row, 4)), IF(VALUE(LEFT([Delayed Result Date]@row, 2)) = "10", "10/31" + "/" + VALUE(RIGHT([Delayed Result Date]@row, 4)),IF(VALUE(LEFT([Delayed Result Date]@row, 2)) = "11", "11/30" + "/" + VALUE(RIGHT([Delayed Result Date]@row, 4)),.....)

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    Thanks, @Razetto...

    There really are so many right ways ;)

    I've been coached to create a formula that adds a month and subtracts a day - I think to ensure the occasional Feb 29 happens. It looks like the AI formula is trying to do this, but I'm not yet skilled enough in date formulas to figure out how to make it right.

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Razetto
    Razetto ✭✭✭✭✭✭

    @Meredith Rhodes I think that the AI formula is assuming that Delayed Result Date is a date column and not text. Have you tried testing it using a date column?

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    @Razetto This could be.

    If I force that column to be a date column, it makes the date the first of the month.

    Hmmm... maybe this is it.

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @Meredith Rhodes

    Try this

    =DATE(YEAR([Delayed Result Date]@row), MONTH([Delayed Result Date]@row) + 1, 1) - 1

    This forces the date to the first day of the next month, then subtracts one day from that - which is the last day of the month of your current month.

    Will this work for you?

    Kelly

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    UPDATE:

    When I force my text column with 09/2022 to be a date column, it becomes 09/01/22.

    When I ask AI to add one month to this and substract one day it gives me =DATE(YEAR([Delayed Result Date]@row), MONTH([Delayed Result Date]@row) + 1, DAY([Delayed Result Date]@row) - 1)

    This has a #Invalid Data Type error.

    Ideas?

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    @Kelly Moore - I'm getting an Invalid data type error. Do you know what the potential sources of this error are?

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Meredith Rhodes

    As suggested by @Razetto, have you verified that both your [Delayed Result Date]@row column and the column you are pasting this formula into are actually Date type columns. Check your column properties to verify.

    Also, the formula shown you showed immediately above is not the formula I provided. Once your column properties are verified, please copy paste the formula below into the appropriate column.

    DATE(YEAR([Delayed Result Date]@row), MONTH([Delayed Result Date]@row) + 1, 1) - 1

    Does this work for you?

    Kelly

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    Hi @Kelly Moore -

    I can confirm that I have turned my source column into a date column and that I have copied your exact formula into the cell.

    Still, Invalid Data Type.

    I DO have a formula in the Delayed Result Date cell to extract only the numbers out of the cell next to it. Maybe this is the problem. I'll try a helper cell that copies the date.

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Meredith Rhodes

    As a test, in your formula column, please try this formula

    =DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)-1

    What does this give you?

    Kelly

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    This gives me 04/30/24.

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    The column with the date I'm referencing has this formula in it:

    =IF(NOT(ISBLANK([Delay Type]@row)), MID([Delay Type]@row, FIND("Extension:", [Delay Type]@row) + 11, LEN([Delay Type]@row)), "")

    To extract the numbers from the "Extension:" text.

    So I think this must be the issue.

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    This is perplexing.

    Meredith Rhodes, PhD

    ClinicalTrials.gov Specialist

    UW School of Medicine & Public Health

    UW Clinical Trials Institute

    mkrhodes@clinicaltrials.wisc.edu

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @Meredith Rhodes

    Your date in the Delayed Result Date is not a real date - it is a textstring that looks like a date.

    Change your current formula in the Delay Type to this:

    =DATE(VALUE(RIGHT([Delay Type]@row, 4)), VALUE(MID([Delay Type]@row, FIND("/", [Delay Type]@row) - 2, 2)), 1)

    This should fix the other column.

    Kelly

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @Meredith Rhodes

    Oh, don't forget to add the beginning IF statement

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!