Comparing Dates

  • Thread starter Thread starter Darin Spence
  • Start date Start date
D

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
 
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.
 
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
 
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.
 
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
 
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.
 
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.
 
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.
 
Back
Top