The MIN and MAX functions can provide easy ways to capture current dates.

When you build reporting models you often automate the current month based on the latest data imported. This saves one less input and can automate the whole reporting process.

Understanding dates in Excel

In Excel dates are numbers. Number 1 was 1 Jan 1900. If you type in 44444 in a cell and format it as a date you will see that it was 5 September 2021 (may the fours be with you).

Since dates are numbers, we can use the MIN function (stands for minimum) to find the earliest date. We can use the MAX function (stands for maximum) to find the latest date.   

If column A in the Data sheet contains the dates for a data set, then this simple formula extracts the latest date from that column.

=MAX(Data!A:A)

You will need to format the cell to display the result as a date.

If the MoreData sheet also has dates in column A, you could use this formula to find the latest date between the two sheets.

=MAX(Data!A:A,MoreData!A:A)

If you need to find the earliest, latest date between the two data sets you could use.

=MIN(MAX(Data!A:A),MAX(MoreData!A:A))

Each MAX function finds the latest date from its data set. The MIN function finds the earliest from those two dates.

Both MIN and MAX ignore blank/empty and text cells in their ranges.

Zero

MAX can be useful to avoid negative numbers. If negatives aren’t permitted in a cell and zero is displayed for all negatives, you could use a formula like.

=MAX(A1,0)

If A1 contains a positive number, this formula will display the A1 value. If A1 contains a negative, then zero is returned.