| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To work out how many days, weeks, months to I retire on 20 Sep 201
Hi, thanks to any one who can help.
I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep 201
Hi,
With your retirement date in a1 try =DATEDIF(TODAY(),A1,"y")&" Years "&DATEDIF(TODAY(),A1,"ym")&" Months" Mike "Using the date functions" wrote: Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep 201
For the weeks to retire, it's:
=(a1-today())/7 Format as number Regards, Fred. "Mike H" wrote in message ... Hi, With your retirement date in a1 try =DATEDIF(TODAY(),A1,"y")&" Years "&DATEDIF(TODAY(),A1,"ym")&" Months" Mike "Using the date functions" wrote: Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep 201
With retire date in A1
Days =DATEDIF(TODAY(),A16,"d") Weeks = =DATEDIF(TODAY(),A16,"d")/7 Month =DATEDIF(TODAY(),A16,"m") Years =DATEDIF(TODAY(),A16,"y") If this post helps click Yes --------------- Jacob Skaria "Using the date functions" wrote: Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep 201
Press Alt+F11 to go to the VB editor, click Insert/Module from its menu bar,
then copy paste the following UDF (User Defined Function) into the code window that opened up (see rest of my message after the code)... '*************** START OF CODE *************** Function YMWD(ByVal Date1 As Variant, ByVal Date2 As Variant) As String Dim TempDate As Date Dim NumOfYears As Long Dim NumOfMonths As Long Dim NumOfWeeks As Long Dim NumOfDays As Long If IsDate(CDate(Date1)) And IsDate(CDate(Date2)) Then Date1 = CDate(Date1) Date2 = CDate(Date2) If Date1 Date2 Then TempDate = Date1 Date1 = Date2 Date2 = TempDate End If NumOfYears = DateDiff("yyyy", Date1, Date2) Date1 = DateSerial(Year(Date2), Month(Date1), Day(Date1)) If Date1 Date2 Then Date1 = DateAdd("yyyy", -1, Date1) NumOfYears = NumOfYears - 1 End If NumOfMonths = DateDiff("m", Date1, Date2) Date1 = DateSerial(Year(Date2), Month(Date2), Day(Date1)) If Date1 Date2 Then Date1 = DateAdd("m", -1, Date1) NumOfMonths = NumOfMonths - 1 End If NumOfDays = Abs(DateDiff("d", Date1, Date2)) NumOfWeeks = NumOfDays \ 7 NumOfDays = NumOfDays Mod 7 If NumOfYears 0 Then YMWD = CStr(NumOfYears) & " year" & _ IIf(NumOfYears = 1, "", "s") End If If NumOfMonths 0 Then If YMWD < "" Then YMWD = YMWD & ", " YMWD = YMWD & CStr(NumOfMonths) & " month" & _ IIf(NumOfMonths = 1, "", "s") End If If NumOfWeeks 0 Then If YMWD < "" Then YMWD = YMWD & ", " YMWD = YMWD & CStr(NumOfWeeks) & " week" & _ IIf(NumOfWeeks = 1, "", "s") End If If NumOfDays 0 Then If YMWD < "" Then YMWD = YMWD & ", " YMWD = YMWD & CStr(NumOfDays) & " day" & _ IIf(NumOfDays = 1, "", "s") ElseIf YMWD = "" Then YMWD = "0 Days" End If YMWD = RTrim$(YMWD) End If End Function '*************** END OF CODE *************** Now, go back to a worksheet and type this in... =YMWD(A1,TODAY()) where I assume A1 contains your retirement date. You can also just put your retirement date in as text like this... =YMWD("20 Sep 2010",TODAY()) The UDF I gave you actually is more general than you asked for... it will calculate the Years, Months, Weeks and Days between any two dates... just put the dates in as arguments to the UDF (the order doesn't matter) and the difference in Years, Months, Weeks and Days will be returned to you. Also note that the UDF puts the "s" on multiple units and leaves it off for single units (that is, for example, "1 Week", but "2 Weeks"). -- Rick (MVP - Excel) "Using the date functions" <Using the date wrote in message ... Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep
"Mike H" wrote: Hi, With your retirement date in a1 try =DATEDIF(TODAY(),A1,"y")&" Years "&DATEDIF(TODAY(),A1,"ym")&" Months" Mike "Using the date functions" wrote: Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks Thanks Mike h The header date should be 2015, I found your reply very informative. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep
"Fred Smith" wrote: For the weeks to retire, it's: =(a1-today())/7 Format as number Regards, Fred. "Mike H" wrote in message ... Hi, With your retirement date in a1 try =DATEDIF(TODAY(),A1,"y")&" Years "&DATEDIF(TODAY(),A1,"ym")&" Months" Mike "Using the date functions" wrote: Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks Thanks Fred Smith I seem to have messed up on the date, it should be 2015, I've learn't alot. In a very short space of time. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep
"Jacob Skaria" wrote: With retire date in A1 Days =DATEDIF(TODAY(),A16,"d") Weeks = =DATEDIF(TODAY(),A16,"d")/7 Month =DATEDIF(TODAY(),A16,"m") Years =DATEDIF(TODAY(),A16,"y") If this post helps click Yes --------------- Jacob Skaria "Using the date functions" wrote: Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks Many Thanks Jacob. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
To work out how many days, weeks, months to I retire on 20 Sep
"Rick Rothstein" wrote: Press Alt+F11 to go to the VB editor, click Insert/Module from its menu bar, then copy paste the following UDF (User Defined Function) into the code window that opened up (see rest of my message after the code)... '*************** START OF CODE *************** Function YMWD(ByVal Date1 As Variant, ByVal Date2 As Variant) As String Dim TempDate As Date Dim NumOfYears As Long Dim NumOfMonths As Long Dim NumOfWeeks As Long Dim NumOfDays As Long If IsDate(CDate(Date1)) And IsDate(CDate(Date2)) Then Date1 = CDate(Date1) Date2 = CDate(Date2) If Date1 Date2 Then TempDate = Date1 Date1 = Date2 Date2 = TempDate End If NumOfYears = DateDiff("yyyy", Date1, Date2) Date1 = DateSerial(Year(Date2), Month(Date1), Day(Date1)) If Date1 Date2 Then Date1 = DateAdd("yyyy", -1, Date1) NumOfYears = NumOfYears - 1 End If NumOfMonths = DateDiff("m", Date1, Date2) Date1 = DateSerial(Year(Date2), Month(Date2), Day(Date1)) If Date1 Date2 Then Date1 = DateAdd("m", -1, Date1) NumOfMonths = NumOfMonths - 1 End If NumOfDays = Abs(DateDiff("d", Date1, Date2)) NumOfWeeks = NumOfDays \ 7 NumOfDays = NumOfDays Mod 7 If NumOfYears 0 Then YMWD = CStr(NumOfYears) & " year" & _ IIf(NumOfYears = 1, "", "s") End If If NumOfMonths 0 Then If YMWD < "" Then YMWD = YMWD & ", " YMWD = YMWD & CStr(NumOfMonths) & " month" & _ IIf(NumOfMonths = 1, "", "s") End If If NumOfWeeks 0 Then If YMWD < "" Then YMWD = YMWD & ", " YMWD = YMWD & CStr(NumOfWeeks) & " week" & _ IIf(NumOfWeeks = 1, "", "s") End If If NumOfDays 0 Then If YMWD < "" Then YMWD = YMWD & ", " YMWD = YMWD & CStr(NumOfDays) & " day" & _ IIf(NumOfDays = 1, "", "s") ElseIf YMWD = "" Then YMWD = "0 Days" End If YMWD = RTrim$(YMWD) End If End Function '*************** END OF CODE *************** Now, go back to a worksheet and type this in... =YMWD(A1,TODAY()) where I assume A1 contains your retirement date. You can also just put your retirement date in as text like this... =YMWD("20 Sep 2010",TODAY()) The UDF I gave you actually is more general than you asked for... it will calculate the Years, Months, Weeks and Days between any two dates... just put the dates in as arguments to the UDF (the order doesn't matter) and the difference in Years, Months, Weeks and Days will be returned to you. Also note that the UDF puts the "s" on multiple units and leaves it off for single units (that is, for example, "1 Week", but "2 Weeks"). -- Rick (MVP - Excel) "Using the date functions" <Using the date wrote in message ... Hi, thanks to any one who can help. I'm using Excell 2000 and would like to do the above in a exsisting wookbook. Sad I know, but my other hobby is digital photography, and that is takeing all my thoughts. Thanks Many Thanks Rick, I have never used the VB Editor before so I just had to try your solution and it workes just fine |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheets dates,weeks and Months. How do I | Excel Worksheet Functions | |||
| Calculate Number of Months Weeks and Days Between Two Dates | Excel Worksheet Functions | |||
| A number of days into weeks and days | Excel Worksheet Functions | |||
| Group data by weeks not months | Excel Discussion (Misc queries) | |||
| How do I sort by date (not days, weeks, months) in Excel 2000? | Excel Discussion (Misc queries) | |||
Linear Mode
