Dates - Comparing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Earl Partridge - 01 Mar 2008 03:44 GMT

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

Reply to this Message

Tyro - 01 Mar 2008 04:34 GMT

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

Reply to this Message

Earl Partridge - 01 Mar 2008 04:51 GMT

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

"Earl Partridge" <earlpNG@pearnet.com> wrote in message news:ux2Qc50eIHA.5996@TK2MSFTNGP04.phx.gbl...
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

Reply to this Message

Tyro - 01 Mar 2008 05:05 GMT

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

"Tyro" <Tyro@hotmail.com> wrote in message news:F15yj.59555$Pv2.14762@newssvr23.news.prodigy.net...
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

"Earl Partridge" <earlpNG@pearnet.com> wrote in message news:ux2Qc50eIHA.5996@TK2MSFTNGP04.phx.gbl...
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

Reply to this Message

T. Valko - 01 Mar 2008 07:25 GMT

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.

Signature

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

"Tyro" <Tyro@hotmail.com> wrote in message news:F15yj.59555$Pv2.14762@newssvr23.news.prodigy.net...
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

"Earl Partridge" <earlpNG@pearnet.com> wrote in message news:ux2Qc50eIHA.5996@TK2MSFTNGP04.phx.gbl...
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

Reply to this Message

Dave Peterson - 01 Mar 2008 12:07 GMT

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.

Signature

Dave Peterson

Reply to this Message

Rate this thread: