Dates to days/months/years

Hi folks,

I have around 40 years worth of data and I need to convert the dates into
days, months and years.  Tried several things but they don't seem to work.
I calculated there are 13200 days in total - how can you convert this to
days/months/years?

Big thanks in advance!

Ian


0
ian_rules (8)
11/5/2004 10:35:33 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
763 Views

Similar Articles

[PageSpeed] 49

Ian,

Do you mean you want to "break down" dates in a column to three separate
columns with year, month and date? If yes, functions:

YEAR(somedate)
MONTH(somedate)
DAY(somedate)

will give you what you want. If you meant something else, please clarify.

HTH,
Nikos

"Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
> Hi folks,
>
> I have around 40 years worth of data and I need to convert the dates into
> days, months and years.  Tried several things but they don't seem to work.
> I calculated there are 13200 days in total - how can you convert this to
> days/months/years?
>
> Big thanks in advance!
>
> Ian
>
>


0
11/5/2004 11:19:04 AM
Sorry, what I meant was: for example: say I have dates between 1/1/1990 and
1/2/1993, which totals 761 days.  How would you translate this into days,
months and years (i.e..e. end up with a result of 2 years, 1 month, 0 days)?

Thanks again in advance!

Ian


"Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
news:ugsgUkywEHA.4004@tk2msftngp13.phx.gbl...
> Ian,
>
> Do you mean you want to "break down" dates in a column to three separate
> columns with year, month and date? If yes, functions:
>
> YEAR(somedate)
> MONTH(somedate)
> DAY(somedate)
>
> will give you what you want. If you meant something else, please clarify.
>
> HTH,
> Nikos
>
> "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
> > Hi folks,
> >
> > I have around 40 years worth of data and I need to convert the dates
into
> > days, months and years.  Tried several things but they don't seem to
work.
> > I calculated there are 13200 days in total - how can you convert this to
> > days/months/years?
> >
> > Big thanks in advance!
> >
> > Ian
> >
> >
>
>


0
ian_rules (8)
11/5/2004 12:19:50 PM
Ignore the last post, posted with errors!!

Sorry, what I meant was: for example: say I have dates between 1/1/1990 and
1/2/1992, which totals 761 days.  How would you translate this into days,
months and years (i.e. end up with a result of 2 years, 1 month, 0 days)?

Thanks again in advance!

Ian


"Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
news:ugsgUkywEHA.4004@tk2msftngp13.phx.gbl...
> Ian,
>
> Do you mean you want to "break down" dates in a column to three separate
> columns with year, month and date? If yes, functions:
>
> YEAR(somedate)
> MONTH(somedate)
> DAY(somedate)
>
> will give you what you want. If you meant something else, please clarify.
>
> HTH,
> Nikos
>
> "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
> > Hi folks,
> >
> > I have around 40 years worth of data and I need to convert the dates
into
> > days, months and years.  Tried several things but they don't seem to
work.
> > I calculated there are 13200 days in total - how can you convert this to
> > days/months/years?
> >
> > Big thanks in advance!
> >
> > Ian
> >
> >
>
>


0
ian_rules (8)
11/5/2004 12:24:29 PM
Ian,

This is easy if you are willing to go for an approximation, like:

Years: C1 = INT((B1-A1)/365)
Months: D1 = INT(((B1-A1)-C1*365)/30)
Days: E1 = B1-A1-C1*365-D1*30

HTH,
Nikos

If you want to go for accuracy, taking into account leap uears and actual
days in each month, then I'm afraid it will take a custom function in VB.
"Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
news:GdKid.24425$Fu2.21817@fe1.news.blueyonder.co.uk...
> Sorry, what I meant was: for example: say I have dates between 1/1/1990
and
> 1/2/1993, which totals 761 days.  How would you translate this into days,
> months and years (i.e..e. end up with a result of 2 years, 1 month, 0
days)?
>
> Thanks again in advance!
>
> Ian
>
>
> "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
> news:ugsgUkywEHA.4004@tk2msftngp13.phx.gbl...
> > Ian,
> >
> > Do you mean you want to "break down" dates in a column to three separate
> > columns with year, month and date? If yes, functions:
> >
> > YEAR(somedate)
> > MONTH(somedate)
> > DAY(somedate)
> >
> > will give you what you want. If you meant something else, please
clarify.
> >
> > HTH,
> > Nikos
> >
> > "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> > news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
> > > Hi folks,
> > >
> > > I have around 40 years worth of data and I need to convert the dates
> into
> > > days, months and years.  Tried several things but they don't seem to
> work.
> > > I calculated there are 13200 days in total - how can you convert this
to
> > > days/months/years?
> > >
> > > Big thanks in advance!
> > >
> > > Ian
> > >
> > >
> >
> >
>
>


0
11/5/2004 12:47:12 PM
Thanks again.  I had thought of that, but I have to be accurate.  Do you
think there are any other ways?

Ian


"Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
news:%23HzjnVzwEHA.3228@TK2MSFTNGP10.phx.gbl...
> Ian,
>
> This is easy if you are willing to go for an approximation, like:
>
> Years: C1 = INT((B1-A1)/365)
> Months: D1 = INT(((B1-A1)-C1*365)/30)
> Days: E1 = B1-A1-C1*365-D1*30
>
> HTH,
> Nikos
>
> If you want to go for accuracy, taking into account leap uears and actual
> days in each month, then I'm afraid it will take a custom function in VB.
> "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> news:GdKid.24425$Fu2.21817@fe1.news.blueyonder.co.uk...
> > Sorry, what I meant was: for example: say I have dates between 1/1/1990
> and
> > 1/2/1993, which totals 761 days.  How would you translate this into
days,
> > months and years (i.e..e. end up with a result of 2 years, 1 month, 0
> days)?
> >
> > Thanks again in advance!
> >
> > Ian
> >
> >
> > "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
> > news:ugsgUkywEHA.4004@tk2msftngp13.phx.gbl...
> > > Ian,
> > >
> > > Do you mean you want to "break down" dates in a column to three
separate
> > > columns with year, month and date? If yes, functions:
> > >
> > > YEAR(somedate)
> > > MONTH(somedate)
> > > DAY(somedate)
> > >
> > > will give you what you want. If you meant something else, please
> clarify.
> > >
> > > HTH,
> > > Nikos
> > >
> > > "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> > > news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
> > > > Hi folks,
> > > >
> > > > I have around 40 years worth of data and I need to convert the dates
> > into
> > > > days, months and years.  Tried several things but they don't seem to
> > work.
> > > > I calculated there are 13200 days in total - how can you convert
this
> to
> > > > days/months/years?
> > > >
> > > > Big thanks in advance!
> > > >
> > > > Ian
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
ian_rules (8)
11/5/2004 1:04:25 PM
It could be done with custom functions in VB but it's quite tricky.

The Years part is easy: take the start dat and start adding one to the year
part and comparing with the end date, until the end date is surpassed. Years
= number of iterations -1. Example code:
Function cYears(StartDate As Date, EndDate As Date)
If StartDate > EndDate Then
    cYears = "Error!"
    Exit Function
End If
cYears = -1
cTemp = StartDate
Do Until cTemp > EndDate
    cTemp = DateSerial(Year(cTemp) + 1, Month(cTemp), Day(cTemp))
    cYears = cYears + 1
Loop
End Function

The Days part would always be the simplest one, once the Years and Months is
calculated.

The Months part is the real pain. A similar approach to the Years one would
fail if the start date is, say, Jan-30, because there's no Feb-30, so what
do you do there? You could use an array holding the number of days per month
and consult it in each loop, but how you treat it is a user convention, not
a mathematical or programming issue. I suppose that's why the world goes
with the 12 X 30 convention (thus Excel's built-in function DAYS360()).

Nikos

"Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
news:tTKid.24435$Fu2.19294@fe1.news.blueyonder.co.uk...
> Thanks again.  I had thought of that, but I have to be accurate.  Do you
> think there are any other ways?
>
> Ian
>
>
> "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
> news:%23HzjnVzwEHA.3228@TK2MSFTNGP10.phx.gbl...
> > Ian,
> >
> > This is easy if you are willing to go for an approximation, like:
> >
> > Years: C1 = INT((B1-A1)/365)
> > Months: D1 = INT(((B1-A1)-C1*365)/30)
> > Days: E1 = B1-A1-C1*365-D1*30
> >
> > HTH,
> > Nikos
> >
> > If you want to go for accuracy, taking into account leap uears and
actual
> > days in each month, then I'm afraid it will take a custom function in
VB.
> > "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> > news:GdKid.24425$Fu2.21817@fe1.news.blueyonder.co.uk...
> > > Sorry, what I meant was: for example: say I have dates between
1/1/1990
> > and
> > > 1/2/1993, which totals 761 days.  How would you translate this into
> days,
> > > months and years (i.e..e. end up with a result of 2 years, 1 month, 0
> > days)?
> > >
> > > Thanks again in advance!
> > >
> > > Ian
> > >
> > >
> > > "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
> > > news:ugsgUkywEHA.4004@tk2msftngp13.phx.gbl...
> > > > Ian,
> > > >
> > > > Do you mean you want to "break down" dates in a column to three
> separate
> > > > columns with year, month and date? If yes, functions:
> > > >
> > > > YEAR(somedate)
> > > > MONTH(somedate)
> > > > DAY(somedate)
> > > >
> > > > will give you what you want. If you meant something else, please
> > clarify.
> > > >
> > > > HTH,
> > > > Nikos
> > > >
> > > > "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> > > > news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
> > > > > Hi folks,
> > > > >
> > > > > I have around 40 years worth of data and I need to convert the
dates
> > > into
> > > > > days, months and years.  Tried several things but they don't seem
to
> > > work.
> > > > > I calculated there are 13200 days in total - how can you convert
> this
> > to
> > > > > days/months/years?
> > > > >
> > > > > Big thanks in advance!
> > > > >
> > > > > Ian
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
11/5/2004 1:33:52 PM
Use the DATEDIF function, like this:

  =DATEDIF(A1,B1,"y")  for years
  =DATEDIF(A1,B1,"ym") for months in the last partial year
  =DATEDIF(A1,B1,"md") for days in the last partial month
 

On Fri, 05 Nov 2004 12:24:29 GMT, "Ian" <ian_rules@yahoo.NOSPAM.com> wrote:

>Ignore the last post, posted with errors!!
>
>Sorry, what I meant was: for example: say I have dates between 1/1/1990 and
>1/2/1992, which totals 761 days.  How would you translate this into days,
>months and years (i.e. end up with a result of 2 years, 1 month, 0 days)?
>
>Thanks again in advance!
>
>Ian
>
>
>"Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
>news:ugsgUkywEHA.4004@tk2msftngp13.phx.gbl...
>> Ian,
>>
>> Do you mean you want to "break down" dates in a column to three separate
>> columns with year, month and date? If yes, functions:
>>
>> YEAR(somedate)
>> MONTH(somedate)
>> DAY(somedate)
>>
>> will give you what you want. If you meant something else, please clarify.
>>
>> HTH,
>> Nikos
>>
>> "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
>> news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
>> > Hi folks,
>> >
>> > I have around 40 years worth of data and I need to convert the dates
>into
>> > days, months and years.  Tried several things but they don't seem to
>work.
>> > I calculated there are 13200 days in total - how can you convert this to
>> > days/months/years?
>> >
>> > Big thanks in advance!
>> >
>> > Ian
>> >
>> >
>>
>>
>

0
anonymous (74721)
11/5/2004 3:06:17 PM
Yup, that works! Thank you very much!

Ian


"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:dl5no0pgpb3rod6k0oqhuooskhcqckmg1i@4ax.com...
> Use the DATEDIF function, like this:
>
>   =DATEDIF(A1,B1,"y")  for years
>   =DATEDIF(A1,B1,"ym") for months in the last partial year
>   =DATEDIF(A1,B1,"md") for days in the last partial month
>
>
> On Fri, 05 Nov 2004 12:24:29 GMT, "Ian" <ian_rules@yahoo.NOSPAM.com>
wrote:
>
> >Ignore the last post, posted with errors!!
> >
> >Sorry, what I meant was: for example: say I have dates between 1/1/1990
and
> >1/2/1992, which totals 761 days.  How would you translate this into days,
> >months and years (i.e. end up with a result of 2 years, 1 month, 0 days)?
> >
> >Thanks again in advance!
> >
> >Ian
> >
> >
> >"Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
> >news:ugsgUkywEHA.4004@tk2msftngp13.phx.gbl...
> >> Ian,
> >>
> >> Do you mean you want to "break down" dates in a column to three
separate
> >> columns with year, month and date? If yes, functions:
> >>
> >> YEAR(somedate)
> >> MONTH(somedate)
> >> DAY(somedate)
> >>
> >> will give you what you want. If you meant something else, please
clarify.
> >>
> >> HTH,
> >> Nikos
> >>
> >> "Ian" <ian_rules@yahoo.NOSPAM.com> wrote in message
> >> news:VHIid.24020$Fu2.3344@fe1.news.blueyonder.co.uk...
> >> > Hi folks,
> >> >
> >> > I have around 40 years worth of data and I need to convert the dates
> >into
> >> > days, months and years.  Tried several things but they don't seem to
> >work.
> >> > I calculated there are 13200 days in total - how can you convert this
to
> >> > days/months/years?
> >> >
> >> > Big thanks in advance!
> >> >
> >> > Ian
> >> >
> >> >
> >>
> >>
> >
>


0
ian_rules (8)
11/6/2004 6:41:45 PM
Reply:

Similar Artilces: