To work out how many days, weeks, months to I retire on 20 Sep 201

  • Thread starter Using the date functions
  • Start date
U

Using the date functions

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
 
M

Mike H

Hi,

With your retirement date in a1 try

=DATEDIF(TODAY(),A1,"y")&" Years "&DATEDIF(TODAY(),A1,"ym")&" Months"

Mike
 
F

Fred Smith

For the weeks to retire, it's:
=(a1-today())/7
Format as number

Regards,
Fred.
 
J

Jacob Skaria

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
 
R

Rick Rothstein

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
(e-mail address removed)> wrote in message
 
U

Using the date functions

Mike H said:
Hi,

With your retirement date in a1 try

=DATEDIF(TODAY(),A1,"y")&" Years "&DATEDIF(TODAY(),A1,"ym")&" Months"

Mike
Click to expand...

Thanks Mike h
The header date should be 2015, I found your reply very informative.
 
U

Using the date functions

Fred Smith said:
For the weeks to retire, it's:
=(a1-today())/7
Format as number

Regards,
Fred.
Click to expand...
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.
 
U

Using the date functions

Jacob Skaria said:
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
Click to expand...

Many Thanks Jacob.
 
U

Using the date functions

Rick Rothstein said:
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
(e-mail address removed)> wrote in message
Click to expand...
Many Thanks Rick, I have never used the VB Editor before so I just had to
try your solution and it workes just fine
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How many days fall in each month? 4
how do I work out age in years, months, days in excel 1
UDF weeks of inventory calculation - lost on how to create this lo 3
How create & use list of date-based tasks to use on each new case? 4
How to recognize changes in slope of data? 2
Been Having A Problem for a Few Months... How To Turn On "Create Folder" 1
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..) 8
Sorry Dupe post ...RE:Dual Boot Vista on external drives.... 2

Top