Dates - Comparing

  • Thread starter Thread starter Earl Partridge
  • Start date Start date
E

Earl Partridge

Windows XP, Office 97 (or Office 2003)
Trying to track and record the number of visits a person has made. Plus, record the date of
all their visits. Here's what I'd like to do.

Have Column D record the last date a person attended, and Column E to show the total visits.
I want to enter the latest date in Column D, visibly replacing that date, but retaining history for
all dates visited.

I want to program Column E to recognize that Column D now contains a later date than the
date it replaced, and increment Column E by 1.

My programming skills are better than my Excel skills, so I envision something like:
if D2 > old value then E2 = E2 + 1

Actually, what would work better would be the ability to select multiple names and apply the
same criteria to those selected. I'm expecting responses to recommend using a database.
I can manage a database ok but I'm also trying to learn the capabilities of Excel.

Earl
 
Dates are whole numbers in Excel. January 1, 1900 is day 1. Today, February 29, 2008 is day 39507. If the date includes a time of day, the time is a decimal fraction.
You can manipulate the dates as you please.

Tyro

Windows XP, Office 97 (or Office 2003)
Trying to track and record the number of visits a person has made. Plus, record the date of
all their visits. Here's what I'd like to do.

Have Column D record the last date a person attended, and Column E to show the total visits.
I want to enter the latest date in Column D, visibly replacing that date, but retaining history for
all dates visited.

I want to program Column E to recognize that Column D now contains a later date than the
date it replaced, and increment Column E by 1.

My programming skills are better than my Excel skills, so I envision something like:
if D2 > old value then E2 = E2 + 1

Actually, what would work better would be the ability to select multiple names and apply the
same criteria to those selected. I'm expecting responses to recommend using a database.
I can manage a database ok but I'm also trying to learn the capabilities of Excel.

Earl
 
I have C2 = 12/25/2007
I have D2 = 1/1/2008

In E2 I test: =IF(D2 > C2, K2 = K2 + 1, K2 = K2)
And E2 shows "FALSE"

It appears to be reading the month to determine if greater or not.

I've tried using DateSerial and DateValue with D2 and C2, with no luck.

Earl

Dates are whole numbers in Excel. January 1, 1900 is day 1. Today, February 29, 2008 is day 39507. If the date includes a time of day, the time is a decimal fraction.
You can manipulate the dates as you please.

Tyro

Windows XP, Office 97 (or Office 2003)
Trying to track and record the number of visits a person has made. Plus, record the date of
all their visits. Here's what I'd like to do.

Have Column D record the last date a person attended, and Column E to show the total visits.
I want to enter the latest date in Column D, visibly replacing that date, but retaining history for
all dates visited.

I want to program Column E to recognize that Column D now contains a later date than the
date it replaced, and increment Column E by 1.

My programming skills are better than my Excel skills, so I envision something like:
if D2 > old value then E2 = E2 + 1

Actually, what would work better would be the ability to select multiple names and apply the
same criteria to those selected. I'm expecting responses to recommend using a database.
I can manage a database ok but I'm also trying to learn the capabilities of Excel.

Earl
 
You cannot use a formula like =IF(D2>C2,K2=K2+1,K2=K2) in Excel. A formula in Excel returns a value to the cell that contains the formula. The formula cannot affect other cells. Your formula is saying: If D2 is greater than C2, evaluate K2=K2+1, which is clearly FALSE, and if D2 is not greater than C2 then evaluate K2=K2 which is TRUE. You might wish to read a book on basic Excel.

Tyro


I have C2 = 12/25/2007
I have D2 = 1/1/2008

In E2 I test: =IF(D2 > C2, K2 = K2 + 1, K2 = K2)
And E2 shows "FALSE"

It appears to be reading the month to determine if greater or not.

I've tried using DateSerial and DateValue with D2 and C2, with no luck.

Earl

Dates are whole numbers in Excel. January 1, 1900 is day 1. Today, February 29, 2008 is day 39507. If the date includes a time of day, the time is a decimal fraction.
You can manipulate the dates as you please.

Tyro

Windows XP, Office 97 (or Office 2003)
Trying to track and record the number of visits a person has made. Plus, record the date of
all their visits. Here's what I'd like to do.

Have Column D record the last date a person attended, and Column E to show the total visits.
I want to enter the latest date in Column D, visibly replacing that date, but retaining history for
all dates visited.

I want to program Column E to recognize that Column D now contains a later date than the
date it replaced, and increment Column E by 1.

My programming skills are better than my Excel skills, so I envision something like:
if D2 > old value then E2 = E2 + 1

Actually, what would work better would be the ability to select multiple names and apply the
same criteria to those selected. I'm expecting responses to recommend using a database.
I can manage a database ok but I'm also trying to learn the capabilities of Excel.

Earl
 
You're writing the worksheet formulas like you would write programming code. The worksheet formulas don't work like that. A formula can only "pull" data to the cell that contains the formula. A formula can not "push" data to another cell.

--
Biff
Microsoft Excel MVP


I have C2 = 12/25/2007
I have D2 = 1/1/2008

In E2 I test: =IF(D2 > C2, K2 = K2 + 1, K2 = K2)
And E2 shows "FALSE"

It appears to be reading the month to determine if greater or not.

I've tried using DateSerial and DateValue with D2 and C2, with no luck.

Earl

Dates are whole numbers in Excel. January 1, 1900 is day 1. Today, February 29, 2008 is day 39507. If the date includes a time of day, the time is a decimal fraction.
You can manipulate the dates as you please.

Tyro

Windows XP, Office 97 (or Office 2003)
Trying to track and record the number of visits a person has made. Plus, record the date of
all their visits. Here's what I'd like to do.

Have Column D record the last date a person attended, and Column E to show the total visits.
I want to enter the latest date in Column D, visibly replacing that date, but retaining history for
all dates visited.

I want to program Column E to recognize that Column D now contains a later date than the
date it replaced, and increment Column E by 1.

My programming skills are better than my Excel skills, so I envision something like:
if D2 > old value then E2 = E2 + 1

Actually, what would work better would be the ability to select multiple names and apply the
same criteria to those selected. I'm expecting responses to recommend using a database.
I can manage a database ok but I'm also trying to learn the capabilities of Excel.

Earl
 
So maybe you want this formula in E2:

=IF(D2 > C2, K2 + 1, K2)

It retrieves the value from K2 and either adds one or not.
 
Back
Top