+ Reply to Thread
Results 1 to 3 of 3

Set day first day of the week (Half way to solved already - I think!)

  1. #1
    Registered User
    Join Date
    10-31-2011
    Location
    Tyne and Wear
    MS-Off Ver
    Excel 2007
    Posts
    5

    Set day first day of the week (Half way to solved already - I think!)

    I have an issue with trying to create 3 date ranges automatically within excel. (cannot use the analysis pack functions due restrictions for other users of this workbook)

    The date ranges I require are Mon-Sun, Fri-Thurs, Sat-Fri

    I have already managed to get Mon-Sun working (See below)

    Basically there are 2 columns

    I have 2 defined ranges to called WDF (Weekly date from) and WDT (Weekly Date to)

    A1 =WDT

    B1 =IF(A1="","",IF(INT(A1)-WEEKDAY(A1,3)+6>WDT,WDT,INT(A1)-WEEKDAY(A1,3)+6))

    This formula is filled down as far as I required (8 rows but no relevant)

    A2 =IF(B1="","",IF(B1+1>WDT,"",B1+1))

    What the above does is ensure that no matter what date range the user chooses I will have a weekly dates from 1st date (WDF) to the Sunday within the same week and them Mon-Sun for each respective week until the WDT is reached anything else will blank the cells.

    My problem has arisen when trying to amend this formula to do have the same result Fri-Thur and Sat-Fri

    Any help would be appreicated.

    Thanks

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Set day first day of the week (Half way to solved already - I think!)

    If you want the end of the week to be Friday try this formula for B1 copied down

    =IF(A1="","",MIN(WDT,A1+7-WEEKDAY(A1+1)))

    changing like this will give you Thursday for end of week

    =IF(A1="","",MIN(WDT,A1+7-WEEKDAY(A1+2)))

    change the +2, to +3, +4 etc. for other days
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-31-2011
    Location
    Tyne and Wear
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Set day first day of the week (Half way to solved already - I think!)

    That works perfectly, thanks you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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