+ Reply to Thread
Results 1 to 9 of 9

Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

  1. #1
    Forum Contributor
    Join Date
    05-14-2012
    Location
    England
    MS-Off Ver
    Excel 2010/2013
    Posts
    100

    Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    Hi I've included a workbook which hopefully will explain this better but here goes:
    I need to check a date in a cell to see if it falls in a certain period of the year.
    The year is split into 13 periods of 4 weeks each starting on 15/03/15 this year.
    So for example today (28/03/15) is Period 2

    Please if anyone can help I'd really appreciate it.

    Thanks,

    Kevin.
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,524

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    will you provide the result manually for 1 or 2 instances
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,342

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    Does attachment work?
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    See attachment...
    Attached Files Attached Files
    Last edited by jhren; 03-28-2015 at 02:06 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    Consider applying conditional formatting to Period due column to indicate due this period and next.

    Added example into above attachment.
    Last edited by jhren; 03-28-2015 at 02:06 PM.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,524

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    C2=IF(B2<TODAY(),"","P "&INT((B2-1-DATE(YEAR(B2)-(MONTH(B2)<3),3,15)+1)/28)+1&" of "&YEAR(B2)-(MONTH(B2)<3))

    D2=IF(INT((B2-1-DATE(YEAR(B2)-(MONTH(B2)<3),3,15)+1)/28)=INT((TODAY()-1-DATE(YEAR(B2)-(MONTH(B2)<3),3,15)+1)/28),"Yes","No")

    E2=IF(INT((B2-1-DATE(YEAR(B2)-(MONTH(B2)<3),3,15)+1)/28)=INT((TODAY()-1-DATE(YEAR(B2)-(MONTH(B2)<3),3,15)+1)/28+1),"Yes","No")
    TRY THIS

  7. #7
    Forum Contributor
    Join Date
    05-14-2012
    Location
    England
    MS-Off Ver
    Excel 2010/2013
    Posts
    100

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    Thanks everyone "nflsales" was the one that worked best as I couldn't load the other examples back saying they were corrupted?

    I appreciate all the help

    Kevin.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,524

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    You are welcome and thanks for your feedback and adding reputation

  9. #9
    Forum Contributor
    Join Date
    05-14-2012
    Location
    England
    MS-Off Ver
    Excel 2010/2013
    Posts
    100

    Re: Does cell A1 fall between X & Y dates and Splitting year into 13 periods of four weeks

    No, Thank you! My head was exploding!

+ 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. Pull dates from multiple columns that fall into the same month/year.
    By safrau in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2014, 12:15 PM
  2. [SOLVED] Require a formula to see if 2 dates fall within Year end and Start
    By john dalton in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-06-2014, 09:12 AM
  3. Splitting Semester and Year into 2 columns (Fall '10)
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-17-2012, 03:08 PM
  4. Replies: 4
    Last Post: 01-20-2010, 10:10 AM
  5. Replies: 2
    Last Post: 07-12-2009, 04:52 PM

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