Excel Formula Translation

[richardlpalmer]

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.

 

[SkipVought]

Hi,

What is the value]/b] in A2 and what value is returned?

The formula below looks at a cell full of text "dates"

Are you certain that these values are TEXT?

What happens if you change the FORMATTING of the cell to GENERAL?

Please report back on EACH of these questions.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

So you have

2009-03-27 00:00:00
2009-04-10 00:00:00
2009-04-29 00:00:00
2009-05-13 00:00:00
2009-05-27 00:00:00

all in a single cell? Yech.

It looks like you are using an array formula to split it apart.

I don't know anything about SharePoint, but the first question is: does it support array formulas? If not, then the logic this formula uses can't be replicated in SharePoint.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[richardlpalmer]

Skip, the value of A2 is:

2009-03-27 00:00:00
2009-04-10 00:00:00
2009-04-29 00:00:00
2009-05-13 00:00:00
2009-05-27 00:00:00

It's all one text cell.

I did this in Excel as an experiment - the data is in a text column within SharePoint. So there's no real option to "change the FORMATTING of the cell to GENERAL" as it's already in the most general format - text.

AH, I know, it's ugly.

So far this is what I've come up with (but it doesn't work yet). I decided to just try the first part of the formula in SharePoint - you know, work it in pieces.

=MIN(IF(MID([Dates],(ROW(INDIRECT("1:"&(LEN[Dates]+1)/20))-1)*20+1,19)+0>=[TODAY](),"true"))

I know SharePoint wants certain things in brackets not parenthesis.

 

[SkipVought]

What is the point of the experiment, other than academic?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[richardlpalmer]

Oh, sorry about the misunderstanding. I was originally trying to accomplish this in SharePoint but haven't been able to find support. So I duplicated the problem in Excel and was able to quickly find a solution (above). This was the experiment -- can it be accomplished in Excel?

Now what I'm trying to accomplish is the translation of this solution into something SharePoint can use. Take a look at my first post again and you'll see.

Formulas that work in Excel usually work in SharePoint after some minor tweaks. In fact, the training on SharePoint specifically references Excel when it comes to formulating calculations.

 

[SkipVought]

But WHY would your have multiple dates in one cell? What is the Business Case for such a travesty?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

In Excel you could split these up into multiple cells using Text-to-Columns. Is there anything like that available in SharePoint?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[richardlpalmer]

Looking at this from a database perspective (and Excel would work with this as well) you can have data in a couple of ways.

1 to many

1 to 1

I have a very large list that is "1 to 1". There isn't an option of converting it to a "1 to many".

I have a form (InfoPath) where Users can select many pre-set date options for upcoming releases. The info from InfoPath is published to my SharePoint list in a single line text column. It's basically adding data to one "cell" of a "row" of data -- to put it in Excel terms.

So, I have my 1 to 1 (a row) and now there's one column (a cell) with many items in it - topic of this thread. Ultimately (for reporting purposes) I need to know what the "next release date" is - and that will be calculated based on the dates originally selected in the InfoPath form.

Now before you ask, I could have the InfoPath form do this calculation but it would only work once - when it's initially input. The actuality is that every few weeks someone is going to look for the "next release date" for every item in this list (hundreds). So the data has to be gathered within SharePoint dynamically.

So Excel is not even being used, nor will it be. Excel isn't an option to be used, nor will it be. This is all being done in SharePoint.

I used Excel because it's a much faster route to figure out a formula (and the support has been wonderful) than to try to do it in SharePoint.

 

[richardlpalmer]

Any ideas at all?

 

[AnotherHiggins]

Can SharePoint do array formulas?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[richardlpalmer]

I'm not sure how to answer that. Array formulas are an Excel thing, aren't they? I don't know how that translates to SharePoint...

 

[AnotherHiggins]

-> Array formulas are an Excel thing, aren't they? I don't know how that translates to SharePoint...

Ya got me. I don't know anything about SP.

An array formula is basically one that can evaluate each item in a list or each cell in a range (an array) instead of a single cell.

The formula from your OP is using a clever 'cheat' with the ROWS to break the text up into 20 character chunks (you can see that the 20 is 'hard-coded') in the formula.


FYI: You can select portions of a formula and press [F9] to evaluate just that portion. This can be helpful in deciphering a large or complicated formula.

The formula you're trying to translate winds up looking like this:
=MIN(IF({"2009-03-27 00:00:00";"2009-04-10 00:00:00";"2009-04-29 00:00:00";"2009-05-13 00:00:00";"2009-05-27 00:00:00"} + 0 >= TODAY(), {"2009-03-27 00:00:00";"2009-04-10 00:00:00";"2009-04-29 00:00:00";"2009-05-13 00:00:00";"2009-05-27 00:00:00"} + 0))

The array formula is recursively looking through each of the date strings and comparing them to TODAY (note: the "+0" forces the text into a number). The MIN at the beginning takes the lowest date in the array that is greater than TODAY.

I'm not sure if that helps you at all or not, but it's a brief explanation of what the formula is doing.

- - -

If there is the equivalent to Text To Columns in sharepoint, that would give us another way to tackle the problem since we could use it to break each date out into a separate cell.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[richardlpalmer]

That does help. Thank you.

I was hoping a SharePoint person would also see this thread as I know Excel and SP don't translate exactly but much of the functionality does/can.

If you have any ideas on a good SP help source I'll be very receptive...

 

[cmeagan656]

You could try the SharePoing forum in Tek-Tips.

There is also a SharePoint Forum however, since I don't use SharePoint I'm not sure if it will have the help that you're looking for.

Cheers.

 

[richardlpalmer]

Thanks. I originally started this quest in the SharePoint forum in Tek-Tips but received nothing in reply. I looked elsewhere (including the other forum you referenced) and the only area I could find help was in Excel, which helped me with the above formula.

As MOSS is an Office product I thought it would be best to try here...