Skip to main content
Solved

Can a formula reference one cell instead of a column?

  • March 12, 2020
  • 7 replies
  • 63 views
B

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?

Best answer by Zollie

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')

7 replies

B
  • Brittanie_Ander
  • Author
  • New Participant
  • March 13, 2020

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.

Z
Forum|alt.badge.img+18
  • Zollie
  • Inspiring
  • March 13, 2020

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.

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • March 13, 2020

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'
)
Z
Forum|alt.badge.img+18
  • Zollie
  • Inspiring
  • Answer
  • March 13, 2020

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')

kuovonne
B
  • Brittanie_Ander
  • Author
  • New Participant
  • March 13, 2020

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.

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • March 13, 2020

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.

Z
Z
Forum|alt.badge.img+18
  • Zollie
  • Inspiring
  • March 13, 2020

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 :slightly_smiling_face:

Terms & ConditionsAccessibility statement