Skip to main content

I have a formula for calculating when I can plant seeds, according to the LFD (last frost date), which is and will remain 3/29/2020. Weeks is how many weeks before the LFD I can plant the seeds. The formula:


DATEADD({LFD},-{Weeks Start},‘weeks’)


Currently I have a whole column for LFD that is set to autofill 3/29/2020 into every row, of which I have 350+. Instead of this whole column for LFD, is there a way I can somehow reference this number one time within a formula, or within one cell somewhere instead of a column of 350 rows with the same number?

Hi, thanks for answering. Would you mind showing me how to apply your response to my example? I’m new to this and I read over what you wrote a few times but I don’t understand how to apply it to my issue at all. Ideally, I would like to find a way to change LFD to 3/29/2020 within the formula, but I wasn’t able to get it to recognize it as a date, so the formula would not compute.

Hi, thanks for answering. Would you mind showing me how to apply your response to my example? I’m new to this and I read over what you wrote a few times but I don’t understand how to apply it to my issue at all. Ideally, I would like to find a way to change LFD to 3/29/2020 within the formula, but I wasn’t able to get it to recognize it as a date, so the formula would not compute.


You’re right, that didn’t help at all upon re-reading.


Are you just looking to replace {LFD} with some date string so that you don’t need it as a stand-alone date field? If so, I’m testing it out now.


Otherwise, trying to imagine what your table looks like to suss out the end-goal.

Hi, thanks for answering. Would you mind showing me how to apply your response to my example? I’m new to this and I read over what you wrote a few times but I don’t understand how to apply it to my issue at all. Ideally, I would like to find a way to change LFD to 3/29/2020 within the formula, but I wasn’t able to get it to recognize it as a date, so the formula would not compute.


To change LFD to a static date in the formula, use the DATETIME_PARSE function.


DATEADD( 

  DATETIME_PARSE("3/29/2020", 'MM/DD/YYYY'), 

  -{Weeks Start},

  'weeks'

)

Hi, thanks for answering. Would you mind showing me how to apply your response to my example? I’m new to this and I read over what you wrote a few times but I don’t understand how to apply it to my issue at all. Ideally, I would like to find a way to change LFD to 3/29/2020 within the formula, but I wasn’t able to get it to recognize it as a date, so the formula would not compute.


Is this what you’re looking for?


DATEADD(DATESTR("2020-03-29"),-{Weeks Start},'week')

You’re right, that didn’t help at all upon re-reading.


Are you just looking to replace {LFD} with some date string so that you don’t need it as a stand-alone date field? If so, I’m testing it out now.


Otherwise, trying to imagine what your table looks like to suss out the end-goal.


Thank you! That completely solved it! What does DATESTR stand for? I kept trying to put in different formats of 03-29-2020 with no luck, so I made that unnecessary column for it. I had no idea about DATESTR.

Is this what you’re looking for?


DATEADD(DATESTR("2020-03-29"),-{Weeks Start},'week')


It’s neat to find out that DATESTR can output a datetime when nested in another function. I didn’t know that.

Thank you! That completely solved it! What does DATESTR stand for? I kept trying to put in different formats of 03-29-2020 with no luck, so I made that unnecessary column for it. I had no idea about DATESTR.


DATESTR stands for ‘date string.’ A string is just some text inside quotes. DATESTR takes that string and converts it to a ‘datetime’ data type. While you and I might see the characters 03-29-2020 as a date, it isn’t interpreted as such outright. Functions like DATESTR, or DATETIME_PARSE as @kuovonne demonstrated, help with all that type conversion. Data type errors are pretty common. For example, some functions expect a number 123 and will error out when they receive a string with a number in it instead "123". Finicky stuff. Happy planting 🙂

Reply


Terms & Conditions