How to Create Formula to return Fiscal Week

Mona Singh

I have 1 column with Start Date for Fiscal year & week

2nd column is end date for fiscal year & week

I want it to return 3rd column where i have FYYRWK

This is all housed in a separate smartsheet.

Ive tried a few different articles on smartsheet, but cant seem to get one where i want it to be = or Greater than & less than

Seems to be a vlookup, + if statement, but need help.

=if(date) is greater than or equal to (fyDateStart) and is Less than or equal to (date end) then return column 3.

thanks!

Mona Singh

Paul,

I went back to basics, and a simple vlookup worked, if i moved the "end date" column out of range and selected approx match :)

=VLOOKUP([Start Date]57, {vLookups Events Range 13}, 2, true)

Thank you for your help!

Paul Newcome

Can you provide a screenshot of the current columns you have set up already? There are a couple of different ways this could be done, but the best way is going to depend on exactly what you have.

Mona Singh

This is on a separate smartsheet, where i am housing a lot of info so i can map to multiple sheets as i go.

In my one Event list, i have "Date of event" I want that date to signal and bring back the correct FY-Qtr-WK

Paul Newcome

Ok. Let's give this a go...

=INDEX({Other Sheet FY-QTR-WK}, MATCH(MIN(COLLECT({Other Sheet End Date}, {Other Sheet End Date}, @cell >= [Date of Event]@row)), {Other Sheet End Date}, 0))

Mona Singh

I used this:

=INDEX({vLookups Events Range 13}, MATCH(MIN(COLLECT({vLookups Events Range 3}, {vLookups Events Range 16}, @cell >= [Start Date]@row)), {vLookups Events Range 18}, 0))

It returned no match. :(

For each other sheet end date, should i be referring to cell or column when i select the range?

Paul Newcome

Let's try changing that last range to Range 3...

=INDEX({vLookups Events Range 13}, MATCH(MIN(COLLECT({vLookups Events Range 3}, {vLookups Events Range 16}, @cell >= [Start Date]@row)), {vLookups Events Range 3}, 0))

Mona Singh

No Match :(

=INDEX({vLookups Events Range 13}, MATCH(MIN(COLLECT({vLookups Events Range 16}, {vLookups Events Range 16}, @cell >= [Start Date]@row)), {vLookups Events Range 3}, 0))

Paul Newcome

Your ranges keep changing. The second range in the formula (first after COLLECT function) and the last range in the formula should be the same.

Mona Singh

=INDEX({vLookups Events Range 13}, MATCH(MIN(COLLECT({vLookups Events Range 16}, {vLookups Events Range 16}, @cell >= [Start Date]@row)), {vLookups Events Range 16 }, 0))

Hi Paul,

Sorry - I did try again (See above) and still no match. Double checked that the week existed, but no luck. I'll keep poking around, let me know if you think of another work around.

Mona Singh

Paul,

I went back to basics, and a simple vlookup worked, if i moved the "end date" column out of range and selected approx match :)

=VLOOKUP([Start Date]57, {vLookups Events Range 13}, 2, true)

Thank you for your help!

Paul Newcome

Hmm... That's odd that it din't work. I use almost the same exact concept in a few of my sheets.

What matters is that you were able to get something to work for you.

Please don't forget to mark the most appropriate response(s) as "Helpful" though. This flags them as the "Best Answer" and moves them to the top of the post so that other searching for a similar solution can find it easier.