Reference a date in a non date column

Hi SS peeps,

I am trying to reference a date in a text column by using the date function but it's not working.

=SUMIFS({Employee 1}, {Employee Date of Sep}, >[Jan 5th]45, {Title}, {Lead Sider})

I've tried using

=SUMIFS({Employee 1}, {Employee Date of Sep}, >Date([Jan 5th]45), {Title}, {Lead Sider})

I'm basically wanting to drag the formula across the row and have the date reference update.

Thanks in advance!



Best Answer

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

    Hey @Kyle Keever

    This formula will convert your text string into a usable format. Depending on your global region, you may need to use the semi-colon vs the comma I have shown.

    =SUMIFS({Employee 1}, {Employee Date of Sep}, DATE(VALUE(LEFT([Jan 5th]@row, 4)), VALUE(MID([Jan 5th]@row, 6, 2)), VALUE(RIGHT([Jan 5th]@row, 2))), {Title}, {Lead Sider})

    Will this work for you?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!