+ Reply to Thread
Results 1 to 9 of 9

date manipulation

  1. #1
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    date manipulation

    hi,

    im trying to learn date manipulation thru formulas
    i have a manually inputted date value in A2
    then a weekday function value in B2 =WEEKDAY(A2)

    then a formula in C2 =IF(OR(B2=1,B2=7),DATE(YEAR(A2),MONTH(A2),DAY(A2)-1),A2)
    which shows the previous day of the date value of A2 if B2 is 1 or 7

    how do i include in the formula of C2 to search also the values of N2:N6 which has the same date?

    so in turn in C2 it would show the previous day of the date value of A2 if B2 is 1 or 7 or if the date value of A2 is also in the range of N2:N6


    attached file as suggested
    Attached Files Attached Files
    Last edited by k1dr0ck; 08-16-2018 at 10:09 PM. Reason: attached file

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: date manipulation

    Maybe attach your sample workbook and show your expected result.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: date manipulation

    In F2 then drag down

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: date manipulation

    @kvsrinivasamurthy thanks for the formula!

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: date manipulation

    Thanks for feedback and rep.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: date manipulation

    Not sure if it was deliberate, but check your original formula with a Sunday date A2.

    Then check kvsrinivasamurthy's formula with a Monday date in N2:N6, also with a Friday date.

    Then try this one

    =WORKDAY(A2,-(1-NETWORKDAYS(A2,A2,$N$2:$N$6)),$N$2:$N$6)

    My assumption was that you want the working day on or before the date in A2, with N2:N6 as a list of dates to exclude, but the presence of a Saturday date thwarts that theory.

  7. #7
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: date manipulation

    @jason.b75 a saturday date in A and N (same date) still shows the correct previous working day on C based on the formula of kvsrinivasamurthy

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: date manipulation

    Sorry, Ill try to make it a bit clearer, I had to read my own post twice to make sense of what I said

    Your original question suggested that your formula returns the correct dates, but that you wanted to allow for the dates in column N.
    However, your original formula returned Saturday's date if a Sunday date was entered into column A. Where as kvsrinivasamurthy's formula returns the previous friday.
    Also, I was wondering why you had a Saturday date in column N, if the kvsrinivasamurthy's formula is correct, then a Saturday date should not be needed in column N, This had me thinking that you could be working with a 6 day week instead of 5.

    Note that if you have a Monday date in column N, then kvsrinivasamurthy's formula returns Sunday's date, which I assume is incorrect.
    Also, it returns incorrect results when you have consecutive dates in column N (including Friday and following Monday), check the period of August 10 - 14 in the file.

    My formula works correctly in these cases, but it may not be correct with some other dates based on the points above.

    See attached file with all August dates listed, and some dates highlighted based on the above. The file conatins the original formula, along with kvsrinivasamurthy's and mine to compare results.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: date manipulation

    @jason.b75 i got your point
    i added 2 pair columns(1 column checks the day then the other column moves to the previous day if its a weekend) so that it moves to the previous weekday when its a saturday or sunday

    column N values are not controlled by the user it is generated from other sources and cannot be changed

+ 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. [SOLVED] Date and time manipulation
    By AlexMK in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-30-2016, 02:40 AM
  2. Date Format Manipulation
    By Zagra147 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2009, 05:43 AM
  3. Date manipulation
    By elite_thut in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2008, 03:20 AM
  4. Date manipulation
    By Rick A in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-22-2006, 01:35 PM
  5. Macro Date Manipulation
    By DKY in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-27-2006, 03:45 PM
  6. [SOLVED] For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation
    By vmegha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2005, 08:20 PM
  7. Problem with Date Manipulation
    By Sbufkle in forum Excel General
    Replies: 4
    Last Post: 11-24-2005, 11:45 AM

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