Help with use of DATEDIF for unknown beginning and end dates

[iddqd]

i begin exercising the DATEDIF function to see the date difference.
i have a file that has dates entered into various cells in various columns. there are gaps in between columns and rows so sorting will create a visual mess to me. i just want them to stay where they are.

is there a way to make DATEDIF looks for earlier date and most recent date in entire spreadsheet to find the difference?

[dscheikey]

I didn’t understand exactly what you wanted to achieve.
But since a date is simply a number internally, you can use MIN(), MAX(), MINIFS(), MAXIFS(), SMALL(), LARGE(), AGGREGATE() to analyse your data and determine the correct comparison cells.

[iddqd]

thanks you. i am definitely study your suggestions.

[Villeroy]

@iddqd

there are gaps in between columns and rows

This is a major problem. Organize your data without any gaps.

[iddqd]

currently in progress. thanks.

[Villeroy]

Having a column with dates like
2024-01-01
< empty cells meaning the above date >
2024-01-02
< empty cells meaning the above date
2024-01-03
< empty cells meaning the above date

then there is a trick to fill the empty cells in one go.

1. Select all the cells below (not including) the first value and copy them, say A2:A99 below A1.
2. If A2 is not the current input cell, navigate with the Tab key to A2.
3. Type the formula =A1 into A2 and finish input with Alt+Enter which applies this formula to all selected cells.
4. Without changing the selection, paste-special (Ctrl+Shift+V). In the dialog choose “All” and “Skip empty cells” which restores the previous values and formulas without the gaps.
5. Finally, copy and paste-special again. This time uncheck “Skip empty cells” and click the “Values only” button. This converts the remaining formulas to constant values.

[joshua4]

STEP 5: Copy again with Ctrl+c.
Step 6. Finally, paste-special again. This time uncheck “Skip empty cells” and click the “Values only” button. This converts the remaining formulas to constant values.

[iddqd]

that’s really good to know. will check this soon. thank you.