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
Best Answer
-
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
Answers
-
@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)),.....)
-
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
-
@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?
-
@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
-
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
-
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
-
@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
-
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
-
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
-
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
-
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
-
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
-
This is perplexing.
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
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
-
Oh, don't forget to add the beginning IF statement
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!