+ Reply to Thread
Results 1 to 16 of 16

Split years into weeks and get a date

  1. #1
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Split years into weeks and get a date

    Hi,

    A situation: A meeting have been convening since 17-Sep-1992 on weekly basis.

    How can we get next meeting exact date with formula AND VBA as there may have been difference due to leap years.

    Note: Meeting was never missed on every 7th day. So how can get a list of all the previous meetings as well

    I am expecting a single line code or formula and not loops
    Last edited by ImranBhatti; 10-18-2018 at 03:38 AM.
    Teach me Excel VBA

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Split years into weeks and get a date

    As dates as stored just by counting days since first date of Ecvel calendar (either Jan 1900 or 1904 - depending on settings) and meetings are organized weekly (say every Thursday noon), just 7 could be added to have date of next meeting or substaracted to have a previous meeting date.

    So to have the upcoming meeting you can try formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    if A1 is a date of first meeting

    IN VBA it could be:
    Please Login or Register  to view this content.
    Last edited by Kaper; 10-18-2018 at 03:50 AM.
    Best Regards,

    Kaper

  3. #3
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Split years into weeks and get a date

    Thanks kasper
    It is giving the next date. if the meeting is after 2 weeks do I just need to replace 7 with 14?

    And the other part of my question please
    So how can get a list of all the previous meetings as well

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

    Re: Split years into weeks and get a date

    If, as you say, you have had a meeting every week exactly seven days after the previous meeting, I fail to see why leap years would have affected anything.

    I would say that the starting date in A1 and the this formula in A2 copied down should work: =A1+7

    Excel 2016 (Windows) 32 bit
    A
    1
    17/09/1992
    2
    24/09/1992
    3
    01/10/1992
    4
    08/10/1992
    5
    15/10/1992
    6
    22/10/1992
    7
    29/10/1992
    8
    05/11/1992
    9
    12/11/1992
    10
    19/11/1992
    11
    26/11/1992
    12
    03/12/1992
    13
    10/12/1992
    14
    17/12/1992
    15
    24/12/1992
    16
    31/12/1992
    17
    07/01/1993
    18
    14/01/1993
    19
    21/01/1993
    20
    28/01/1993
    21
    04/02/1993
    22
    11/02/1993
    23
    18/02/1993
    24
    25/02/1993
    25
    04/03/1993
    26
    11/03/1993
    27
    18/03/1993
    28
    25/03/1993
    29
    01/04/1993
    30
    08/04/1993
    31
    15/04/1993
    32
    22/04/1993
    33
    29/04/1993
    34
    06/05/1993
    35
    13/05/1993
    36
    20/05/1993
    37
    27/05/1993
    38
    03/06/1993
    Sheet: Sheet1
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Split years into weeks and get a date

    Yes. You are right Ali. Its just because of fear of working with dates.

    Can we get this huge list of dates with a single line VBA code? as I will need them in an access mini project.

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

    Re: Split years into weeks and get a date

    Alas, I have no idea about VBA Imran, I am afraid.

  7. #7
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Split years into weeks and get a date

    Opps. the second part was for other members who are VBA users. I know you don't that's why I had separated these 2 lines in post#5.

    It should have not embarrassed those who don't know VBA

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

    Re: Split years into weeks and get a date

    I'm not embarrassed, don't worry! For the few occasions I have needed to use VBA, I have been lucky enough to get excellent guidance here, but I've never needed it enough to learn it.

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Split years into weeks and get a date

    Of course with VBA you can "produce" data structure filled with dates.
    let's say you want a vector (1D array - a list) of all dates since the startdate till today+1 year ahead

    Please Login or Register  to view this content.

  10. #10
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Split years into weeks and get a date

    Or may be current meeting date and 10 previous meetings and 10 upcoming:

    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Split years into weeks and get a date

    Thanks kaper as I have mentioned loop should not be used

    here is an example but I am unable to adopt it for getting the dates for every 7th day till the next meeting date

    Please Login or Register  to view this content.
    Last edited by ImranBhatti; 10-18-2018 at 07:38 AM. Reason: kaper's name was corrected.

  12. #12
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Split years into weeks and get a date

    The level of this page is very advance but something like this is needed.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Split years into weeks and get a date

    Do you mean?
    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Split years into weeks and get a date

    Quote Originally Posted by jindon View Post
    Do you mean?
    Please Login or Register  to view this content.
    Simple answer Yes

    Solved.

  15. #15
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Split years into weeks and get a date

    Why no loop?
    if you want to use it as a source for listbox, then this (not tested - writing from a smartphone) shall work:
    Please Login or Register  to view this content.
    But of course it's longer than simple formula evaluation:
    Please Login or Register  to view this content.

  16. #16
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Split years into weeks and get a date

    Yes I adopted the second one. Thanks kaper

+ 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. Create Fiscal weeks over 3 years of data
    By Yameye in forum Excel General
    Replies: 8
    Last Post: 02-13-2018, 11:32 AM
  2. find years with 53 weeks and red line
    By glda19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2017, 06:04 PM
  3. Split Start End date into multiple years
    By tos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2016, 06:42 AM
  4. [SOLVED] Split Month to Date data into Weeks
    By dksodhi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-15-2013, 10:59 PM
  5. Calculate Years, Months Weeks
    By mycon73 in forum Excel General
    Replies: 4
    Last Post: 03-02-2011, 12:02 PM
  6. Counting weeks in years
    By initias in forum Excel General
    Replies: 13
    Last Post: 11-15-2010, 09:48 AM
  7. Weeks and Years
    By Sam_D in forum Excel General
    Replies: 4
    Last Post: 05-18-2010, 03:22 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