Comparing Dates

[Darin Spence]

I have 12 columns with dates in each cell (1 row).

I'd like to write a formula that would look at row of
dates (12 cells worth), determine which date is the most
recent based on "today," then calculate the number of
weeks that have passed since the most recent date
to "today".

Some of the cells will be empty, and not have any dates.
The result could be "0" Weeks have passed (i.e. a date
that is less than one week old).

I'm thinking about looking into arrays for determining
the most recent date, but I'm not familiar with them.

Anyone have any idea how I can accomplish this? Feel
free to post a formula if it's easy for you.

My sheet basically looks like this:

Date1, Date2, Date3, Date4, Date5, Date6, Date7, Date8,
Date9, Date 10, Date 11, Date 12

Then there is a cell that says:
"Weeks since last visit:" 0 Weeks

or

1 Week

etc.

Thanks!
-Darin

 

[Norman Harker]

Hi Darin!

One way:

="Weeks since last visit: " & MAX(INT((TODAY()-MAX(A6:L6))/7),0) & "
Weeks"

Note that since dates are recorded as the number of days since
31-Dec-1899 (using 1900 Date System), a simple maximum will return the
latest date.
Simple deduction gives number of days.
Division by 7 gives weeks.
Taking the integer gives whole weeks.
Putting in a Max of calculated or 0 covers possibility of the date
being later than today.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[GerryK]

Hi Norman,
I could not duplicate this and could certainly make good
use this concept and formula for samples taken in my line
of work.
I populated A6 to L6 with dates and below those cells put
in your posted formula. Is that correct? (I always get 0)
weeks.) What am I missing?
TIA

 

[Norman Harker]

Hi Gerry!

I have populated cells A6:L6 with dates and put the formula in M6.

It works OK

Are your sure that your dates are dates and not text?

Try: =ISTEXT(A6)
If it returns TRUE then you have texts and not dates.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[GerryK]

Norman,
A6 shows as a date so are the rest(populated from
Orlando's calendar) M6 shows "0 weeks" as well!
I'll try some other things in the meantime!
Gerry

 

[Norman Harker]

Hi Gerry!

Are you sure that all dates are earlier than today?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

No, they were not!
That may be the source of my woes!
Thank you!

 

[Norman Harker]

Hi Gerry!

The formula recommended was:

="Weeks since last visit: " & MAX(INT((TODAY()-MAX(A6:L6))/7),0) & "
Weeks"

The "lead in" MAX gives a choice between the formula for counting
weeks and 0. If the date is after today, the formula returns negative,
so 0 is higher.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Thanks ever so much, another OBAN?

 

[Norman Harker]

Hi Gerry!

I'm on VB today (ice cold, of course) as it's around 35 C outside.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.