richardlpalmer
Vendor
Goal: I need help translating a formula that works in Excel into something I can use in a Calculated column in SharePoint. More specifically I need to understand what the pieces of the formula are doing so I can then get it to work in SharePoint.
Scenario: I've been able to find much support for Excel formulas, not so much for SharePoint. The formula below looks at a cell full of text "dates" and is able to compare them as actual dates to today, giving me the next date after today.
=MIN(IF(MID(A2,(ROW(INDIRECT("1:"&(LEN(A2)+1)/20))-1)*20+1,19)+0>=TODAY(),MID(A2,(ROW(INDIRECT("1:"&(LEN(A2)+1)/20))-1)*20+1,19)+0))
And while this formula works in Excel, I ultimately will need it to work in SharePoint. To do that I need to know what each piece of the formula is doing.
Problem: I'm unable to convert this to a calculation that works in SharePoint. I've previously taken simple formulas from Excel, changed the referenced cell name to my column's name and it's always worked. In this case I tried replacing the cell "A2" with my column "DATES", but it doesn't work:
=MIN(IF(MID(DATES,(ROW(INDIRECT("1:"&(LEN(DATES)+1)/20))-1)*20+1,19)+0>=TODAY(),MID(DATES,(ROW(INDIRECT("1:"&(LEN(DATES)+1)/20))-1)*20+1,19)+0))
The real problem is I don't know what the individual pieces of the Excel formula are doing so don't know how to translate it into what SharePoint wants, to function properly.
Any help on breaking this formula down so I can understand the various functions being accomplished would be very appreciated.
Scenario: I've been able to find much support for Excel formulas, not so much for SharePoint. The formula below looks at a cell full of text "dates" and is able to compare them as actual dates to today, giving me the next date after today.
=MIN(IF(MID(A2,(ROW(INDIRECT("1:"&(LEN(A2)+1)/20))-1)*20+1,19)+0>=TODAY(),MID(A2,(ROW(INDIRECT("1:"&(LEN(A2)+1)/20))-1)*20+1,19)+0))
And while this formula works in Excel, I ultimately will need it to work in SharePoint. To do that I need to know what each piece of the formula is doing.
Problem: I'm unable to convert this to a calculation that works in SharePoint. I've previously taken simple formulas from Excel, changed the referenced cell name to my column's name and it's always worked. In this case I tried replacing the cell "A2" with my column "DATES", but it doesn't work:
=MIN(IF(MID(DATES,(ROW(INDIRECT("1:"&(LEN(DATES)+1)/20))-1)*20+1,19)+0>=TODAY(),MID(DATES,(ROW(INDIRECT("1:"&(LEN(DATES)+1)/20))-1)*20+1,19)+0))
The real problem is I don't know what the individual pieces of the Excel formula are doing so don't know how to translate it into what SharePoint wants, to function properly.
Any help on breaking this formula down so I can understand the various functions being accomplished would be very appreciated.