Excel Dates m-dd-yyyy OR mm-dd-yyyy

[flextera]

I have one overview sheet and multiple worksheets with data on them. Right now I am using =SUMPRODUCT(N(INDIRECT("'"&TEXT(N19+(ROW(INDIRECT("1:"&1+N21-N19))-1),"mm-dd-yyyy")&"'!C5"))) as a lookup feature to look through the worksheets using the mm-dd-yyyy tab name. Is there anyway to switch that forumla so it includes either mm-dd-yyyy date tab names AND m-dd-yyyy formated tab names.........Any help would be appreciated.

[FDibbins]

Have you tried just "m-dd-yyyy"

[flextera]

Yes....But I want the tabs to be either mm-dd-yyyy AND m-dd-yyyy and both work...

[flextera]

How could the forumla say look for mm-dd-yyyy AND m-dd-yyyy?

[XOR LX]

I thought you said you would have no problem changing them all to a single format?

Regards

[flextera]

I'm working with people who keep changing from mm-dd-yyyy and m-dd-yyyy....I was wondering if there was a way for it to recognize either formats....

[XOR LX]

I'll tell you what: if you post a small workbook with an example and your expected result clearly outlined then I'll see what I can do.

Otherwise I'd need to mock-up an entire workbook myself just to test potential solutions and I'm afraid I'm not prepared to do that right now (I did last time round but didn't save it).

Regards

[flextera]

Here is a screen capture with what I want. Thanks for any help XOR. you are a god in this excel forum. Capture.PNG

[XOR LX]

Sorry - I meant an actual workbook. I'd still have to re-type everything from your screenshot into Excel in order to test potential solutions.

Regards

[flextera]

How do you post a workbook. All I see is options for an image?

[XOR LX]

Click on Go Advanced and scroll down to Manage Attachments.

Regards

[flextera]

Book1.xlsx Here is my workbook

[flextera]

Thanks for your patients XOR.

[XOR LX]

Thanks for your patients XOR.

You're welcome, but not sure I understand. Did you post the correct attachment?

I mean - there's nothing in C2 or C4 and, more importantly, your tab names are in all the exact same format in this book, i.e. xx-xx-xxxx. I thought you said you had a mixture?

Also, you haven't included your expected result for the formula, so I don't know what I'm aiming for.

I'm afraid I'm going to have to ask you to re-attach.

Regards

[flextera]

Book1.xlsx Hopefully this makes more sense. Right now....the only way to calculate a total for cell G2 in all the worksheets is if I have 04-27-2014....I want to be able to name my wroksheets either 04-27-2014 OR 4-27-2014.

[XOR LX]

You'll have to switch to an array formula**:

=SUM(IFERROR(N(INDIRECT("'"&TEXT(C2+(ROW(INDIRECT("1:"&1+C4-C2))-1),{"m",""}&"m-dd-yyyy")&"'!G2")),0))

By the way, in the workbook you posted the date in C2 (27th April) was the same as the name of that tab, so you had a circular reference, which you can't allow.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[flextera]

Thanks a million XOR. You never cease to amaze.

[XOR LX]

You're welcome again!