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

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." <(E-Mail Removed)> wrote in message
news:0A7B048C-07C2-4B53-BC36-(E-Mail Removed)...
>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
>

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." <(E-Mail Removed)> wrote in message
> news:0A7B048C-07C2-4B53-BC36-(E-Mail Removed)...
> >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
> >
>
>
>

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

Regards,

Ron

"JMB" <(E-Mail Removed)> wrote in message
news:812761D3-8AC6-405F-8C30-(E-Mail Removed)...
>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." <(E-Mail Removed)> wrote in message
>> news:0A7B048C-07C2-4B53-BC36-(E-Mail Removed)...
>> >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
>> >
>>
>>
>>

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
>

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." <(E-Mail Removed)> wrote in message
> news:0A7B048C-07C2-4B53-BC36-(E-Mail Removed)...
> >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
> >
>
>
>

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." <(E-Mail Removed)> wrote in message
news:F085C684-320D-4739-9EE9-(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
>> news:0A7B048C-07C2-4B53-BC36-(E-Mail Removed)...
>> >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
>> >
>>
>>
>>

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." <(E-Mail Removed)> wrote in message
> news:F085C684-320D-4739-9EE9-(E-Mail Removed)...
> > 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." <(E-Mail Removed)> wrote in message
> >> news:0A7B048C-07C2-4B53-BC36-(E-Mail Removed)...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

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

Regards,

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


"Rick S." <(E-Mail Removed)> wrote in message
news:37327FC9-1BC0-46A8-A0E7-(E-Mail Removed)...
>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." <(E-Mail Removed)> wrote in message
>> news:F085C684-320D-4739-9EE9-(E-Mail Removed)...
>> > 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." <(E-Mail Removed)> wrote in message
>> >> news:0A7B048C-07C2-4B53-BC36-(E-Mail Removed)...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>