Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formula resulting in Year, Months & days | Excel Worksheet Functions | |||
Manipulating dates (was Add 6 months to a date) | New Users to Excel | |||
check to see if all dates are for the current year | Excel Programming | |||
count number of months year to date | Excel Worksheet Functions | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) |