+ Reply to Thread
Results 1 to 12 of 12

Split/filter infinite Calendar by weeks

  1. #1
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Split/filter infinite Calendar by weeks

    hello

    i have an employee working shift spreadsheet with an infinite mothly calendar

    now: i would like to now the formulas to show the employee shift from top but split by the selectet week starting mondays

    Captura.JPG

    Captura 2.JPG

    i ad the file so you can see better

    HORARIO.xlsm

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Split/filter infinite Calendar by weeks

    Please try at
    C38
    =WORKDAY(DATE($B$32,MATCH('Shift Rotation Schedule'!$B$31,'Hidden Settings'!$A$2:$A$13,0),0),$B$35*5-4)

    D38
    =C38+1
    Copy to the right
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Split/filter infinite Calendar by weeks

    thank you for you quick response

    that is what i was looking for ,

    but it is posible also to automatlly show the same value from each cell under the dates

    exaple : if angel work on the 10th "mm" it should appear same in week filter
    Attachment 635178

    is it a look up funtion ?

    thank you for your assistance one more time

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Split/filter infinite Calendar by weeks

    Please try at
    C39
    =IFERROR(INDEX($C6:$AG6,MATCH(C$38,$C$5:$AG$5,))&"","")

    or
    =IFERROR(INDEX($C$6:$AG$27,MATCH($A39,$A$6:$A$27,),MATCH(C$38,$C$5:$AG$5,))&"","")

  5. #5
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Split/filter infinite Calendar by weeks

    thank you

    yes it worked too ,

    but can i ask you the favor to check for me one more time the dates ,if i select mounth and year it seems donīt match the date in the week section
    it only seems to work o match just with the mounth of june 2019 selecting other mounths or years does not change the dates of week section

    Attachment 635401

    Attachment 635402

    also selecting december it goes to error complete

    Attachment 635403


    I appreciate all your help patience and work

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Split/filter infinite Calendar by weeks

    Quote Originally Posted by bluepomme View Post
    if i select mounth and year it seems donīt match the date in the week section
    I can't download your attached files.
    Please add some data and manually mockup expected result.


    also selecting december it goes to error complete
    DICIEMBRE

    Hidden Settings A13 => DECIEMBRE

  7. #7
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Split/filter infinite Calendar by weeks

    sorry for that .

    i upload one more time as seen oin the pictures when i select the year or mounth , week calendar does not change propertly , it seems only working for june 2019 i alrade tried to change but could not get it to work



    Captura3.JPG






    Captura1.JPG
    Last edited by bluepomme; 08-02-2019 at 09:24 PM.

  8. #8
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Split/filter infinite Calendar by weeks

    DICIEMBRE

    Hidden Settings A13 => DECIEMBRE[/QUOTE]

    my bad i did not see it

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Split/filter infinite Calendar by weeks

    I see, Please try at C38
    =WORKDAY.INTL(DATE($B$32,MATCH('Shift Rotation Schedule'!$B$31,'Hidden Settings'!$A$2:$A$13,0),0),B35,"0111111")

  10. #10
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Split/filter infinite Calendar by weeks

    im sorry it did not work as expected, it still not match
    may you can take another look into the file by yourself

    the formula you give before did better results but just for june 2019

    Attachment 635615

    thank you
    Last edited by bluepomme; 08-03-2019 at 11:16 AM.

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Split/filter infinite Calendar by weeks

    Quote Originally Posted by bluepomme View Post
    im sorry it did not work as expected, it still not match
    This doesn't give me useful information to work on.
    Please provide input and expected result. and I can't download your attached file.

    "Invalid Attachment specified"
    This is what I get

    bb.jpg

    If this does not work, I'm out of ideas. you need to find other help.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Split/filter infinite Calendar by weeks

    Excellent it's all working now

    thank you for all your effort ,

+ 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. Formulas to help split a month into weeks (mon-sun)
    By nwb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-09-2018, 06:19 PM
  2. [SOLVED] Split years into weeks and get a date
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-18-2018, 11:39 AM
  3. [SOLVED] Months to Split in Exact Weeks
    By pchugh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2018, 05:21 AM
  4. Calendar - NEED IT TO BE 7 WORK DAY WEEKS
    By icebeshining in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2017, 12:26 PM
  5. Need a way to split effort across weeks
    By Niks82 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2017, 05:16 PM
  6. Generate calendar weeks in cells
    By excelactuary in forum Excel General
    Replies: 4
    Last Post: 04-13-2016, 03:44 AM

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