What's best practice for managing multiple variations for a date?

krmintx

I'm working on a sheet where we need to capture several variations for each of 3 date columns (e.g., Drafted Date, Reviewed Date, Approved Date, etc.).

For each of those dates, I need to capture the baseline, best case, worst case, current estimate, and actual.

As you can imagine, it's a bit unwieldy visually and a little challenging to summarize.

Can you recommend best practices for setting up and managing date variations? Any templates, reports, or alternative methods for capturing this kind of data you can recommend?

MichaelTCA

Hello @krmintx

I use a source sheet for a long list of dates and orders they are tied to, then reference that data into another sheet for analytics/reporting data.

Then I use INDEX/COLLECT (current), MAX/COLLECT (worst/longest away), and MIN/COLLECT (best/shortest away) to get the values you are asking for.

The baseline and actual might need to be entered manually, but you could use an INDEX/MATCH function for reporting.

Worst case scenario, if you are trying to analyze the data you might need 5 helper columns (baseline, best, worst, current, actual) for each variation.

With the information you have provided, that's pretty much all I can suggest.