+ Reply to Thread
Results 1 to 26 of 26

Require a formula to look at previous and following days to see if they are populated

  1. #1
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Require a formula to look at previous and following days to see if they are populated

    Hi I have a sheet that shows employee abcences, I require a formula that will look to see if a friday and a monday are populated and auto-populate the saturday and sunday to match the run, if the only the monday is populated then the saturday and sunday need to stay blank, if only the friday is populated then I also need the saturday and sunday to stay blank.

    I have attached a sheet with the current display and the desired outcome, any help will be appreciated.

    Cheers

    J
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    Does =IF(C6=C9,C6,"") in C7 not do it?
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

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

    Re: Require a formula to look at previous and following days to see if they are populated

    I think John wants the solution to be integrated into his current formula.
    Ali


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


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Ali, if possible yes please if not then Im open to helper columns.

    JD

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

    Re: Require a formula to look at previous and following days to see if they are populated

    John - it's actually quite difficult to offer a tested solution without the data to which your current formula refers. Can you not provide a more realistic version of your data?

  6. #6
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    The sheet may be too big to post.

    JD

  7. #7
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    Yes, thats fine, so in the bit that checks for sat and sunday returning "", you could say if saturday and offset(2,0)=the same as offset(-1,0), then put the name in, and if sunday, then if offset(1,0) is the same as offset(-2,0) then put the name in, otherwise ""

    Please Login or Register  to view this content.
    It is only the true condition for Saturday and Sunday that needs altering.

    Not that tricky.
    Last edited by nathansav; 05-30-2014 at 05:15 AM.

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

    Re: Require a formula to look at previous and following days to see if they are populated

    It doesn't need to be the entire workbook, but it does need to have a working formula that we can play with. Otherwise anyone helping has to recreate the data, and that would be very time-consuming.

  9. #9
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    Ali, there is no need to see the data for the Saturday Sunday change is there????

  10. #10
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Nathan Sav i have tried your formula but it just keeps coming up with you have entered too few arguments.

    JD

  11. #11
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    I havent test it, basically you need to split your or

    here it is, example cell C7

    =IF(B7="Saturday",IF(OFFSET(C7,-1,0)=OFFSET(C7,2,0),OFFSET(C7,-1,0),IF(B7="Sunday",IF(OFFSET(C7,-2,0)=OFFSET(C7,1,0),OFFSET(C7,-2,0),"REST OF YOUR FORMULA"),"")))

    You could even trust saturdays logic and say if on sunday if the cell above is not blank use the cell above
    Last edited by nathansav; 05-30-2014 at 05:40 AM.

  12. #12
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Guys will try and post a stripped down version of the sheet to let you see where the prblems are occuring.

    JD

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

    Re: Require a formula to look at previous and following days to see if they are populated

    Quote Originally Posted by nathansav View Post
    Ali, there is no need to see the data for the Saturday Sunday change is there????
    With a complex formula such as John's things can and do go wrong when trying to implement a suggested solution. Looks like this is precisely what has happened. As soon as you get the updated workbook, you'll be able to sort it out.

  14. #14
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Guys, here is slightly stripped down version, the user enters the holidays on the booking sheet, I have booked a full years worth to help with the saturday and sunday problem, each month on the calc engine has the date repeated at the end to enable flexiblility for the FYE start and end dates, eveything works ok with the sat and sunday as the exceptions.

    Hope this helps

    JD
    Attached Files Attached Files
    Last edited by john dalton; 05-30-2014 at 07:48 AM.

  15. #15
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    If you can find an easier way to do any of this im open to suggestions.

    J

  16. #16
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    it will be

    =IF(I11="Saturday",IF(OFFSET(J11,-1,0)=OFFSET(J11,2,0),OFFSET(J11,-1,0),""),IF(I11="Sunday",IF(OFFSET(J11,-2,0)=OFFSET(J11,1,0),OFFSET(J11,-2,0),""),"YOUR FORMULA"))

    You can put your formula for the extraction of leave etc minus the saturday sunday returning "" in the space where it says YOUR FORMULA

  17. #17
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    =IF(I11="Saturday",IF(OFFSET(J11,-1,0)=OFFSET(J11,2,0),OFFSET(J11,-1,0),""),IF(I11="Sunday",IF(OFFSET(J11,-2,0)=OFFSET(J11,1,0),OFFSET(J11,-2,0),""),IFERROR(INDEX('Booking Sheet'!$M$3:$M$15,SMALL(IF((($H11)>='Booking Sheet'!$F$3:$F$15)*(($H11)<='Booking Sheet'!$G$3:$G$15)*(($J$9)='Booking Sheet'!$E$3:$E$15),ROW('Booking Sheet'!$F$3:$F$15)-MIN(ROW('Booking Sheet'!$G$3:$G$15))+1,""),1)),"")))

    Bold is the solution back at 10 o'clock this morning, let me know if im not getting owt :o)

  18. #18
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    FORUM_D16.xlsx

    Hi,

    Is this what you are getting at? In column K?

  19. #19
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Nathan, great solution works just as it should, thank you for your help with this as it was a real sticking point to getting this sheet finished.

    Cheers again

    JD

  20. #20
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    No worries, JD.

  21. #21
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Nathan here is a sheet with the current outcome and the desired outcome, if the friday is booked but the monday is not the saturday and sunday need to be blank even if they are booked on the booking sheet.

    Cheers

    JD
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Nathan, here is the test sheet with the desired outcome.

    Thnaks

    JD
    Attached Files Attached Files

  23. #23
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Require a formula to look at previous and following days to see if they are populated

    FORUM_D16.xlsxHI

    Please see attached

  24. #24
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Hi Nathan, here you go. The Explanation is at the top of the Calculations Engine Sheet, any queries don't hesitate to ask.

    Cheers

    JD
    Attached Files Attached Files

  25. #25
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Have a look at this Nathan.

    Cheers

    JD
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    04-02-2010
    Location
    penrith
    MS-Off Ver
    Excel 2007
    Posts
    522

    Re: Require a formula to look at previous and following days to see if they are populated

    Like this if possible please.

    JD
    Attached Files Attached Files

+ 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] Require a formula to indicate how many days fall within a series of months
    By john dalton in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 05-23-2014, 06:39 AM
  2. Formula for Retrieving Previous Days Information
    By deerhunter34 in forum Excel General
    Replies: 4
    Last Post: 12-13-2012, 12:48 PM
  3. [SOLVED] Require Help - Require Formula for Multiple values in both columns
    By krodge in forum Excel General
    Replies: 6
    Last Post: 01-13-2012, 03:42 AM
  4. previous days in a formula
    By Useless_w/_excel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-29-2008, 12:30 AM
  5. Replies: 9
    Last Post: 07-07-2008, 10:34 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