+ Reply to Thread
Results 1 to 7 of 7

Calculating recurring date in following month, calculating # days in that period

  1. #1
    Walterius
    Guest

    Calculating recurring date in following month, calculating # days in that period

    Excel 2002. I have a simple spreadsheet that logs my expenses by month.

    I haven't been able to teach Excel how to calculate the same day next month.
    E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should be
    07/03/05.

    I also need the number of days in that period, e.g. from 6/03/05 to 7/03/05
    = 30 days (because there are 30 days in June). I don't know how to calculate
    that either.

    Attempts to use the EOMONTH function fail, and I can't find anything else
    that looks suitable. I have been using Excel for years and I am still an
    Excel klutz. :-(

    Many thanks for your help.

    Walterius



  2. #2
    Duke Carey
    Guest

    RE: Calculating recurring date in following month, calculating # days

    Instead of EOMONTH(), try EDATE()

    =EDATE(earlier date,1)

    will give you the same DAY next month. However, if you use it on a Jan 31
    start date, it'll give you the lat day of February.

    Then just subtract the two dates to get the # of days between them


    "Walterius" wrote:

    > Excel 2002. I have a simple spreadsheet that logs my expenses by month.
    >
    > I haven't been able to teach Excel how to calculate the same day next month.
    > E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should be
    > 07/03/05.
    >
    > I also need the number of days in that period, e.g. from 6/03/05 to 7/03/05
    > = 30 days (because there are 30 days in June). I don't know how to calculate
    > that either.
    >
    > Attempts to use the EOMONTH function fail, and I can't find anything else
    > that looks suitable. I have been using Excel for years and I am still an
    > Excel klutz. :-(
    >
    > Many thanks for your help.
    >
    > Walterius
    >
    >
    >


  3. #3
    Walterius
    Guest

    Re: Calculating recurring date in following month, calculating # days

    Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
    next month's date.

    But when I subtract the two dates, I get January 30, 1900, instead of 30.
    Viz:

    C2=6/3/2005
    B15=EDATE(C2,1)=7/3/2005 [correct]

    But =B15-C2 = 1/30/1900

    ??? What am I doing wrong?

    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > Instead of EOMONTH(), try EDATE()
    >
    > =EDATE(earlier date,1)
    >
    > will give you the same DAY next month. However, if you use it on a Jan 31
    > start date, it'll give you the lat day of February.
    >
    > Then just subtract the two dates to get the # of days between them
    >
    >
    > "Walterius" wrote:
    >
    > > Excel 2002. I have a simple spreadsheet that logs my expenses by month.
    > >
    > > I haven't been able to teach Excel how to calculate the same day next

    month.
    > > E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should

    be
    > > 07/03/05.
    > >
    > > I also need the number of days in that period, e.g. from 6/03/05 to

    7/03/05
    > > = 30 days (because there are 30 days in June). I don't know how to

    calculate
    > > that either.
    > >
    > > Attempts to use the EOMONTH function fail, and I can't find anything

    else
    > > that looks suitable. I have been using Excel for years and I am still an
    > > Excel klutz. :-(
    > >
    > > Many thanks for your help.
    > >
    > > Walterius
    > >
    > >
    > >




  4. #4
    Myrna Larson
    Guest

    Re: Calculating recurring date in following month, calculating # days

    Format the result cell as General or Numeric rather than as a date.

    On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius" <[email protected]> wrote:

    >Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
    >next month's date.
    >
    >But when I subtract the two dates, I get January 30, 1900, instead of 30.
    >Viz:
    >
    >C2=6/3/2005
    >B15=EDATE(C2,1)=7/3/2005 [correct]
    >
    >But =B15-C2 = 1/30/1900
    >
    >??? What am I doing wrong?
    >
    >"Duke Carey" <[email protected]> wrote in message
    >news:[email protected]...
    >> Instead of EOMONTH(), try EDATE()
    >>
    >> =EDATE(earlier date,1)
    >>
    >> will give you the same DAY next month. However, if you use it on a Jan 31
    >> start date, it'll give you the lat day of February.
    >>
    >> Then just subtract the two dates to get the # of days between them
    >>
    >>
    >> "Walterius" wrote:
    >>
    >> > Excel 2002. I have a simple spreadsheet that logs my expenses by month.
    >> >
    >> > I haven't been able to teach Excel how to calculate the same day next

    >month.
    >> > E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should

    >be
    >> > 07/03/05.
    >> >
    >> > I also need the number of days in that period, e.g. from 6/03/05 to

    >7/03/05
    >> > = 30 days (because there are 30 days in June). I don't know how to

    >calculate
    >> > that either.
    >> >
    >> > Attempts to use the EOMONTH function fail, and I can't find anything

    >else
    >> > that looks suitable. I have been using Excel for years and I am still an
    >> > Excel klutz. :-(
    >> >
    >> > Many thanks for your help.
    >> >
    >> > Walterius
    >> >
    >> >
    >> >

    >



  5. #5
    Walterius
    Guest

    Re: Calculating recurring date in following month, calculating # days

    Thanks again. FYI, it works with Numeric but only works the first time with
    General, because next time it calculates the formula, it changes General
    back to Date.

    In any event, problems solved. Thanks much!

    Walterius

    "Myrna Larson" <[email protected]> wrote in message
    news:[email protected]...
    > Format the result cell as General or Numeric rather than as a date.
    >
    > On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius" <[email protected]>

    wrote:
    >
    > >Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
    > >next month's date.
    > >
    > >But when I subtract the two dates, I get January 30, 1900, instead of 30.
    > >Viz:
    > >
    > >C2=6/3/2005
    > >B15=EDATE(C2,1)=7/3/2005 [correct]
    > >
    > >But =B15-C2 = 1/30/1900
    > >
    > >??? What am I doing wrong?
    > >
    > >"Duke Carey" <[email protected]> wrote in message
    > >news:[email protected]...
    > >> Instead of EOMONTH(), try EDATE()
    > >>
    > >> =EDATE(earlier date,1)
    > >>
    > >> will give you the same DAY next month. However, if you use it on a Jan

    31
    > >> start date, it'll give you the lat day of February.
    > >>
    > >> Then just subtract the two dates to get the # of days between them
    > >>
    > >>
    > >> "Walterius" wrote:
    > >>
    > >> > Excel 2002. I have a simple spreadsheet that logs my expenses by

    month.
    > >> >
    > >> > I haven't been able to teach Excel how to calculate the same day next

    > >month.
    > >> > E.g., the 3rd of June is 06/03/05, so the 3rd of the next month

    should
    > >be
    > >> > 07/03/05.
    > >> >
    > >> > I also need the number of days in that period, e.g. from 6/03/05 to

    > >7/03/05
    > >> > = 30 days (because there are 30 days in June). I don't know how to

    > >calculate
    > >> > that either.
    > >> >
    > >> > Attempts to use the EOMONTH function fail, and I can't find anything

    > >else
    > >> > that looks suitable. I have been using Excel for years and I am still

    an
    > >> > Excel klutz. :-(
    > >> >
    > >> > Many thanks for your help.
    > >> >
    > >> > Walterius
    > >> >
    > >> >
    > >> >

    > >

    >




  6. #6
    Dave Peterson
    Guest

    Re: Calculating recurring date in following month, calculating # days

    I think it changes not when xl recalculates--but when you edit the formula.

    Sometimes xl can be too helpful.

    Walterius wrote:
    >
    > Thanks again. FYI, it works with Numeric but only works the first time with
    > General, because next time it calculates the formula, it changes General
    > back to Date.
    >
    > In any event, problems solved. Thanks much!
    >
    > Walterius
    >
    > "Myrna Larson" <[email protected]> wrote in message
    > news:[email protected]...
    > > Format the result cell as General or Numeric rather than as a date.
    > >
    > > On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius" <[email protected]>

    > wrote:
    > >
    > > >Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
    > > >next month's date.
    > > >
    > > >But when I subtract the two dates, I get January 30, 1900, instead of 30.
    > > >Viz:
    > > >
    > > >C2=6/3/2005
    > > >B15=EDATE(C2,1)=7/3/2005 [correct]
    > > >
    > > >But =B15-C2 = 1/30/1900
    > > >
    > > >??? What am I doing wrong?
    > > >
    > > >"Duke Carey" <[email protected]> wrote in message
    > > >news:[email protected]...
    > > >> Instead of EOMONTH(), try EDATE()
    > > >>
    > > >> =EDATE(earlier date,1)
    > > >>
    > > >> will give you the same DAY next month. However, if you use it on a Jan

    > 31
    > > >> start date, it'll give you the lat day of February.
    > > >>
    > > >> Then just subtract the two dates to get the # of days between them
    > > >>
    > > >>
    > > >> "Walterius" wrote:
    > > >>
    > > >> > Excel 2002. I have a simple spreadsheet that logs my expenses by

    > month.
    > > >> >
    > > >> > I haven't been able to teach Excel how to calculate the same day next
    > > >month.
    > > >> > E.g., the 3rd of June is 06/03/05, so the 3rd of the next month

    > should
    > > >be
    > > >> > 07/03/05.
    > > >> >
    > > >> > I also need the number of days in that period, e.g. from 6/03/05 to
    > > >7/03/05
    > > >> > = 30 days (because there are 30 days in June). I don't know how to
    > > >calculate
    > > >> > that either.
    > > >> >
    > > >> > Attempts to use the EOMONTH function fail, and I can't find anything
    > > >else
    > > >> > that looks suitable. I have been using Excel for years and I am still

    > an
    > > >> > Excel klutz. :-(
    > > >> >
    > > >> > Many thanks for your help.
    > > >> >
    > > >> > Walterius
    > > >> >
    > > >> >
    > > >> >
    > > >

    > >


    --

    Dave Peterson

  7. #7
    Walterius
    Guest

    Re: Calculating recurring date in following month, calculating # days

    Dave, I think you're right. Many thanks to all.
    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > I think it changes not when xl recalculates--but when you edit the

    formula.
    >
    > Sometimes xl can be too helpful.
    >
    > Walterius wrote:
    > >
    > > Thanks again. FYI, it works with Numeric but only works the first time

    with
    > > General, because next time it calculates the formula, it changes General
    > > back to Date.
    > >
    > > In any event, problems solved. Thanks much!
    > >
    > > Walterius
    > >
    > > "Myrna Larson" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Format the result cell as General or Numeric rather than as a date.
    > > >
    > > > On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius" <[email protected]>

    > > wrote:
    > > >
    > > > >Many thanks, Duke. The first part works just fine: I get July 3, 2005

    as
    > > > >next month's date.
    > > > >
    > > > >But when I subtract the two dates, I get January 30, 1900, instead of

    30.
    > > > >Viz:
    > > > >
    > > > >C2=6/3/2005
    > > > >B15=EDATE(C2,1)=7/3/2005 [correct]
    > > > >
    > > > >But =B15-C2 = 1/30/1900
    > > > >
    > > > >??? What am I doing wrong?
    > > > >
    > > > >"Duke Carey" <[email protected]> wrote in message
    > > > >news:[email protected]...
    > > > >> Instead of EOMONTH(), try EDATE()
    > > > >>
    > > > >> =EDATE(earlier date,1)
    > > > >>
    > > > >> will give you the same DAY next month. However, if you use it on a

    Jan
    > > 31
    > > > >> start date, it'll give you the lat day of February.
    > > > >>
    > > > >> Then just subtract the two dates to get the # of days between them
    > > > >>
    > > > >>
    > > > >> "Walterius" wrote:
    > > > >>
    > > > >> > Excel 2002. I have a simple spreadsheet that logs my expenses by

    > > month.
    > > > >> >
    > > > >> > I haven't been able to teach Excel how to calculate the same day

    next
    > > > >month.
    > > > >> > E.g., the 3rd of June is 06/03/05, so the 3rd of the next month

    > > should
    > > > >be
    > > > >> > 07/03/05.
    > > > >> >
    > > > >> > I also need the number of days in that period, e.g. from 6/03/05

    to
    > > > >7/03/05
    > > > >> > = 30 days (because there are 30 days in June). I don't know how

    to
    > > > >calculate
    > > > >> > that either.
    > > > >> >
    > > > >> > Attempts to use the EOMONTH function fail, and I can't find

    anything
    > > > >else
    > > > >> > that looks suitable. I have been using Excel for years and I am

    still
    > > an
    > > > >> > Excel klutz. :-(
    > > > >> >
    > > > >> > Many thanks for your help.
    > > > >> >
    > > > >> > Walterius
    > > > >> >
    > > > >> >
    > > > >> >
    > > > >
    > > >

    >
    > --
    >
    > Dave Peterson




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1