+ Reply to Thread
Results 1 to 9 of 9

Formula to move an entered date on 1, 2 or 3 days

  1. #1
    Registered User
    Join Date
    10-02-2023
    Location
    West Mids
    MS-Off Ver
    O365
    Posts
    5

    Formula to move an entered date on 1, 2 or 3 days

    Hi I'm currently working in Excel 365 and I’m trying to do the following:

    If a date (e.g. A1) corresponds to a date in an array of manually set dates (e.g. B4:B12), then the date in cell A1 should move on one day (A1 + 1).
    If the date in cell A1 corresponds to a date in another array of manually set dates e.g. E4:E12 then the date in cell A1 should move on 2 days (A1 + 2),
    If the date in cell A1 corresponds to a date in another array of manually set dates e.g. H4:H7 then the date in cell A1 should move on 3 days (A1 + 3).
    If the date in cell A1 does not correspond to any of the arrays, it will stay the same.

    I would be really grateful for some help on the formula

    Many thanks

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,964

    Re: Formula to move an entered date on 1, 2 or 3 days

    Welcome to the forum.

    You cannot change the value of A1 in the way you describe without hard coding that date into a formula in A1 or have a formula in A1 refer to a date entered into another cell. If you want to manually enter a date into A1 and then have it change, you'll need VBA. Please confirm what you want.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-02-2023
    Location
    West Mids
    MS-Off Ver
    O365
    Posts
    5

    Re: Formula to move an entered date on 1, 2 or 3 days

    Many thanks Ali, thats useful

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,964

    Re: Formula to move an entered date on 1, 2 or 3 days

    So which do you want? Because it will affect which section this should be in.

  5. #5
    Registered User
    Join Date
    10-02-2023
    Location
    West Mids
    MS-Off Ver
    O365
    Posts
    5

    Re: Formula to move an entered date on 1, 2 or 3 days

    Hi Ali,
    I wanted to manually enter a date into A1 and then have it change (although it would be agreeable for the returned date to appear in a separate cell (say B1).

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,964

    Re: Formula to move an entered date on 1, 2 or 3 days

    I think you need to ask for a VBA solution, then. Shall I move this to the VBA section for you?

  7. #7
    Registered User
    Join Date
    10-02-2023
    Location
    West Mids
    MS-Off Ver
    O365
    Posts
    5

    Re: Formula to move an entered date on 1, 2 or 3 days

    Many thanks, if you wouldn't mind.

    and thanks again for such a prompt reply.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,964

    Re: Formula to move an entered date on 1, 2 or 3 days

    All done - someone else will take it from here.

  9. #9
    Registered User
    Join Date
    10-02-2023
    Location
    West Mids
    MS-Off Ver
    O365
    Posts
    5

    Re: Formula to move an entered date on 1, 2 or 3 days

    Many thanks Ali

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIFS with days since entered date
    By Maverickhan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-27-2022, 01:32 PM
  2. Replies: 1
    Last Post: 09-07-2021, 09:57 PM
  3. Automatically generate date 90 days from adjacent column date entered
    By louhazosc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-31-2021, 09:51 AM
  4. [SOLVED] Count work days with formula but stop once a date is entered
    By wjeans in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-10-2020, 10:34 AM
  5. [SOLVED] Move row to new tab based on a date being entered
    By hazelwouldbe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2016, 08:58 AM
  6. move row to another sheet if ANY date (or value) is entered
    By microstargem in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-08-2015, 07:52 AM
  7. Replies: 7
    Last Post: 07-01-2014, 01:09 PM

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