Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    increment date automatically (Excel 2002)

    Hi,

    I need some help here. Here's my scenario. I have 2 columns, which are PurchaseDate and ServiceDate. I want to be able to key in PurchaseDate and have ServiceDate set automatically to 6 months later, depending on PurchaseDate's date. Can anyone please help?

    Do appreciate any help offered. Thanks a lot.

  2. #2
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Excel 2002)

    Hi Freekzilla,

    if you want it to move 183 days further then the formula is (assuming PurchaseDate is in a1)
    =a1+183
    Format the cell as "date".

    If you want the same date 6 months later, e.g. 12.Jan.2004 - 12.Jul.2004
    =DATEVALUE(DAY(A1)&"."&MONTH(A1)+6&"."&YEAR(A1))
    Also here format the cell as "date".

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Excel 2002)

    Hi,
    my formula was wrong anyway, because it does not work with months bigger than 6. (maybe too much wine last night)

    Just in case, here is the correction. Obviously Hans' solution is the better one.
    =IF(MONTH(A1)<6,DATEVALUE(DAY(A1)&"."&MONTH(A1)+6& "."&YEAR(A1)),DATEVALUE(DAY(A1)&"."&MONTH(A1)-6&"."&YEAR(A1)+1))

  4. #4
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Excel 2002)

    Hi Wolf,

    Thanks a lot for the reply. I followed this formula of yours =DATEVALUE(DAY(A1)&"."&MONTH(A1)+6&"."&YEAR(A1))
    But I and got this error : #VALUE!

    I highlighted the whole B column and enter the formula into the "insert function" text box above. What I get is #VALUE!
    Can you please help?

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Excel 2002)

    Hi Freakzilla,

    I posted the correction at the same time you posted.

    Anyhow, why not trying Hans' solution as well, it might be much easier.

  6. #6
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Excel 2002)

    Hi Wolf and Hans,

    Thanks a lot for the input.

    As Wolf said, Hans' solution is the better one and I'll opt for that one. But there's a problem. The formula specifies specific cells. I would like to be able to select the whole B column to automatically insert dates that are inserted in the corresponding A column. Meaning B2 will have A2's 6 months later date, B3 will have A3's 6 months later date, etc...

    As I applied Hans' formula, I got this value such as 182 and 37835. Aren't these values supposed to be dates?

    I'm sorry if my question sounds simple. I've never used Excel before. Do appreciate it if you can provide more detailed explanation.

    Thanks a lot.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Excel 2002)

    Hello Wolf,

    Your formula has another problem: if the date entered is (for example) 31 August 2003, the formula will try to generate 31 February 2004, but that is not a valid date. And it is dependent on the user's date settings - you use d/m/y order in the string you evaluate with DATEVALUE, but in the USA this will be interpreted as m/d/y.

  8. #8
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Excel 2002)

    Hi Hans,

    I promise: less wine in the future before posting.

    Anyhow, your formula has a typo
    =IF(A2="","",EDATE(A1,6))
    must be
    =IF(A2="","",EDATE(A2,6))

    For Freakzilla :
    -copy the corrected formula in b2
    -format b2 as date (Format-Cells-date.....select your date format)
    -copy the formula in b2 down till the end of your list.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Excel 2002)

    Perhaps too much wine too! Thanks.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Excel 2002)

    Edited to correct formula - thanks to <!profile=Wolf>Wolf<!/profile> for pointing out the mistake.

    The Analysis Toolpak add-in contains a function EDATE that can be used to add a number of months to a date. To use it, you must make sure that Analysis Toolpak is checked in Tools | Add-Ins...

    Say that the first PurchaseDate is in cell A2, and you want the corresponding ServiceDate in cell B2. Enter the following formula in B2:<pre>=IF(A2="","",EDATE(A2,6))</pre>

    and fill down as far as necessary. If A2 is still empty, B2 will be empty too, but as soon as a date is entered in A2, B2 will contain the date 6 months later.

  11. #11
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Excel 2002)

    Hi Wolf and Hans,

    Thanks for all the formulas. The EDATE worked fine.

    One problem still persists. Using the formula, I have to apply the formula to every cell that I want the date to be automatically inserted. Is there a way to make all B cells to automatically insert the six-months-later date?

    Please help. Thanks a lot.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Excel 2002)

    The alternative would be not to use formulas, but the On Change event of the worksheet:
    <UL><LI>Activate the Visual Basic Editor (Alt+F11)
    <LI>Activate the Project Explorer (Ctrl+R)
    <LI>If necessary, expand the active workbook until you see ThisWorkbook and the individual worksheet objects.
    <LI>Double click the node corresponding to the worksheet you're working on.
    <LI>Paste the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("A:A"))
    If IsDate(oCell) Then
    oCell.Offset(0, 1) = DateAdd("m", 6, oCell)
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    If you want it to work on another column than column A, or on part of the column, adapt both instances of Range("A:A")
    <LI>Switch back to Excel (Alt+F11 or use the Windows task bar)[/list]This is roughly equivalent to the After Update code I posted in the Access forum.

Posting Permissions

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