Dates: How to check if date value is 10 months and or 1 year old?
 

I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs to be
checked for expiration, is it 10 months and or 1 year old from current date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year old?
 

Start by visiting Chip Pearson's coverage of Excel's unexplainably
undocumented DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

Then...for a date in A1...perhaps a formula like this:
=CHOOSE(SUM((DATEDIF(TODAY(),A1,"M")={10,12})*{1,2 })+1,"na","10 mths","1
year")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Rick S." wrote in message
...
I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs to
be
checked for expiration, is it 10 months and or 1 year old from current
date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year o
 

I think the OP is wanting to know if A1 is 10 or 12 months prior to today's
date. If so, he'll need a small change to:

=DATEDIF(A1, TODAY(),"M")

Although he should pick up on that if he follows up on your suggestion to
read the link you provided.


"Ron Coderre" wrote:

Start by visiting Chip Pearson's coverage of Excel's unexplainably
undocumented DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

Then...for a date in A1...perhaps a formula like this:
=CHOOSE(SUM((DATEDIF(TODAY(),A1,"M")={10,12})*{1,2 })+1,"na","10 mths","1
year")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Rick S." wrote in message
...
I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs to
be
checked for expiration, is it 10 months and or 1 year old from current
date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year o
 

Ya know...after reading it again....I think you're right!

Regards,

Ron

"JMB" wrote in message
...
I think the OP is wanting to know if A1 is 10 or 12 months prior to today's
date. If so, he'll need a small change to:

=DATEDIF(A1, TODAY(),"M")

Although he should pick up on that if he follows up on your suggestion to
read the link you provided.


"Ron Coderre" wrote:

Start by visiting Chip Pearson's coverage of Excel's unexplainably
undocumented DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

Then...for a date in A1...perhaps a formula like this:
=CHOOSE(SUM((DATEDIF(TODAY(),A1,"M")={10,12})*{1,2 })+1,"na","10 mths","1
year")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Rick S." wrote in message
...
I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs
to
be
checked for expiration, is it 10 months and or 1 year old from current
date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year old?
 

Excellent! Thanks to all!

--
Regards

Rick
XP Pro
Office 2007



"Rick S." wrote:

I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs to be
checked for expiration, is it 10 months and or 1 year old from current date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year o
 

The things most of you on this BB can do amaze me, on that note, I am flat
out failing to understand the use of the function provided on Chips website.

Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function

I have worked out a scenrio via DateDif (worksheet) but there are to many
dates to check in this manner. Any help on the use of the above function
will be appreciated.

--
Regards

Rick
XP Pro
Office 2007



"Ron Coderre" wrote:

Start by visiting Chip Pearson's coverage of Excel's unexplainably
undocumented DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

Then...for a date in A1...perhaps a formula like this:
=CHOOSE(SUM((DATEDIF(TODAY(),A1,"M")={10,12})*{1,2 })+1,"na","10 mths","1
year")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Rick S." wrote in message
...
I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs to
be
checked for expiration, is it 10 months and or 1 year old from current
date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year o
 

This might be easier to work with:

Public Function CurrentAge(dBirthdate As Date) As Integer
CurrentAge = DateDiff("yyyy", dBirthdate, Date)
End Function

See the DATEDIFF function in VBA help (note the 2 F's)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Rick S." wrote in message
...
The things most of you on this BB can do amaze me, on that note, I am flat
out failing to understand the use of the function provided on Chips
website.

Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function

I have worked out a scenrio via DateDif (worksheet) but there are to many
dates to check in this manner. Any help on the use of the above function
will be appreciated.

--
Regards

Rick
XP Pro
Office 2007



"Ron Coderre" wrote:

Start by visiting Chip Pearson's coverage of Excel's unexplainably
undocumented DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

Then...for a date in A1...perhaps a formula like this:
=CHOOSE(SUM((DATEDIF(TODAY(),A1,"M")={10,12})*{1,2 })+1,"na","10 mths","1
year")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Rick S." wrote in message
...
I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs
to
be
checked for expiration, is it 10 months and or 1 year old from current
date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year o
 

I have much to learn about the use of Functions. The info you supplied along
with reading about Datediff (2 f's) did help me understand the format for
Datediff itself.

Thank you for your time and help!!!

--
Regards

Rick
XP Pro
Office 2007



"Ron Coderre" wrote:

This might be easier to work with:

Public Function CurrentAge(dBirthdate As Date) As Integer
CurrentAge = DateDiff("yyyy", dBirthdate, Date)
End Function

See the DATEDIFF function in VBA help (note the 2 F's)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Rick S." wrote in message
...
The things most of you on this BB can do amaze me, on that note, I am flat
out failing to understand the use of the function provided on Chips
website.

Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function

I have worked out a scenrio via DateDif (worksheet) but there are to many
dates to check in this manner. Any help on the use of the above function
will be appreciated.

--
Regards

Rick
XP Pro
Office 2007



"Ron Coderre" wrote:

Start by visiting Chip Pearson's coverage of Excel's unexplainably
undocumented DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

Then...for a date in A1...perhaps a formula like this:
=CHOOSE(SUM((DATEDIF(TODAY(),A1,"M")={10,12})*{1,2 })+1,"na","10 mths","1
year")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Rick S." wrote in message
...
I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date needs
to
be
checked for expiration, is it 10 months and or 1 year old from current
date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007

Dates: How to check if date value is 10 months and or 1 year o
 

You're very welcome, Rick....and thanks so much for the feedback.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Rick S." wrote in message
...
I have much to learn about the use of Functions. The info you supplied
along
with reading about Datediff (2 f's) did help me understand the format for
Datediff itself.

Thank you for your time and help!!!

--
Regards

Rick
XP Pro
Office 2007



"Ron Coderre" wrote:

This might be easier to work with:

Public Function CurrentAge(dBirthdate As Date) As Integer
CurrentAge = DateDiff("yyyy", dBirthdate, Date)
End Function

See the DATEDIFF function in VBA help (note the 2 F's)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Rick S." wrote in message
...
The things most of you on this BB can do amaze me, on that note, I am
flat
out failing to understand the use of the function provided on Chips
website.

Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function

I have worked out a scenrio via DateDif (worksheet) but there are to
many
dates to check in this manner. Any help on the use of the above
function
will be appreciated.

--
Regards

Rick
XP Pro
Office 2007



"Ron Coderre" wrote:

Start by visiting Chip Pearson's coverage of Excel's unexplainably
undocumented DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

Then...for a date in A1...perhaps a formula like this:
=CHOOSE(SUM((DATEDIF(TODAY(),A1,"M")={10,12})*{1,2 })+1,"na","10
mths","1
year")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Rick S." wrote in message
...
I have read so many posts but i am not grasping the use of dates.
My problem seems simple, in my mind, but it is not in excel.
I have a worksheet that will have dates in a range and each date
needs
to
be
checked for expiration, is it 10 months and or 1 year old from
current
date
(current date is every/any day I use the workbook).

Any help is appreciated!

--
Regards

Rick
XP Pro
Office 2007