Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Date Manipulation

  1. #1
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In my first cell I have a date, say
    1/1/2001
    In the next cell I want the date at the end of the month(I can't use EOmonth):
    1/31/2001

    VBA has the DateAdd function which is perfect but I can't use it in the spreadsheet. Help!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default


    =DATE(YEAR(A1),MONTH(A1)+1,0)

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-01 13:41, Corticus wrote:
    In my first cell I have a date, say
    1/1/2001
    In the next cell I want the date at the end of the month(I can't use EOmonth):
    1/31/2001

    VBA has the DateAdd function which is perfect but I can't use it in the spreadsheet. Help!
    Why can't you use EOMonth??

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))-1
    It's never too late to learn something new.

    Ricky

  5. #5
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes!!

    =Date(year(A1),Month(A1)+1),Day(A1))-1

    worked perfect,
    Thanks for the replies!!
    By the way, I couldn't use EoMonth because I send this spreadsheet all over the state and if the analysis toolkit wasn't available on someone's system, it would be a big pain.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-02 05:52, Corticus wrote:
    Yes!!

    =Date(year(A1),Month(A1)+1),Day(A1))-1

    worked perfect,
    Thanks for the replies!!
    By the way, I couldn't use EoMonth because I send this spreadsheet all over the state and if the analysis toolkit wasn't available on someone's system, it would be a big pain.
    Are you sure?

    Given

    2/2/2001 in A1

    the above formula computes

    3/1/2001

    while

    =EOMONTH(A1,0)

    and

    =DATE(YEAR(A1),MONTH(A1)+1,0)

    both computes:

    2/28/2001

    Aladin

  7. #7
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Fortunately, I usually start with the first day of the month, but not always. So if anyone knows a way to use EOmonth(which is what I want) without having the Analysis Toolkit installed, that would be great.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-02 10:55, Corticus wrote:
    Fortunately, I usually start with the first day of the month, but not always. So if anyone knows a way to use EOmonth(which is what I want) without having the Analysis Toolkit installed, that would be great.
    Hmmm... Obviously, you don't like:

    =DATE(YEAR(A1),MONTH(A1)+1,0)


  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or you could do the recipient a favor and install it for them (it comes w/ Excel):

    Code:
    Private Sub auto_open()
    If AddIns("Analysis toolpak").Installed = False _
    Then AddIns("Analysis toolpak").Installed = True
    End Sub

  10. #10
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not that I intended for this question to get so involved but I would like to have the recipients auto_open the Analysis Toolkit, unfortunately the code:

    Private Sub auto_open()
    If AddIns("Analysis toolpak").Installed = False _
    Then AddIns("Analysis toolpak").Installed = True
    End Sub

    gives me a Run-Time 1004 error, Unable to set the installed property of the add-in class.

    I can do pretty well with the initial equation provided above, I'm just digging now.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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