Divide month into four parts

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Divide month into four parts

Post by YasserKhalil »

Hello everyone

We know that months are different in number of days in each month ..
How can I divide any month to four parts ..based on the number of days in that month? and define which part we are in ..?
Example:
say I have this date 10 Dec 2019

To calculate the number of days of this date

Code: Select all

Day(Application.EoMonth(Date, 0))
How we can divide the Dec month (which is 31 days) to four parts equally ..? and put the extra days to the end
I mean this is 31 days so the four parts should be 7 - 8 - 8 - 8

so first part would be from 1 to 7 ** second part would be 8 to 15 ** third part would be 16 to 23 ** fourth part would be 24 to 31

Now what I need is to define the day which is 10 Dec 2019 in which part of the four and return 1 or 2 or 3 or 4 according to the division clarified
so the expected result for 10 Dec 2019 >> the final output would be 2 as 10 is in the second part

Thanks advanced for help

User avatar
macropod
4StarLounger
Posts: 508
Joined: 17 Dec 2010, 03:14

Re: Divide month into four parts

Post by macropod »

A 30-day month would be more evenly divided into 7 8 7 8 rather than 7 7 8 8. Which do you want?
Paul Edstein
[Fmr MS MVP - Word]

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Divide month into four parts

Post by YasserKhalil »

As for the 30 days would be 7 7 8 8 ..

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Divide month into four parts

Post by HansV »

Code: Select all

Function GetPart(dtm As Date) As Long
    Dim d As Long
    Dim n As Long
    d = Day(dtm)
    n = Day(DateSerial(Year(dtm), Month(dtm) + 1, 0))
    Select Case n
        Case 28
            ' OK
        Case 29
            d = d + (d > 28)
        Case 30
            d = d + (d > 15) + (d > 23)
        Case 31
            d = d + (d > 14) + (d > 22) + (d > 30)
    End Select
    GetPart = (d - 1) \ 7 + 1
End Function
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Divide month into four parts

Post by YasserKhalil »

Thank you very much my tutor
Can this be done using formulas? .. (Kind of curious)

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Divide month into four parts

Post by HansV »

With a date in A1:

=LOOKUP(DAY(A1),SWITCH(DAY(EOMONTH(A1,0)),30,{1,8,15,23},31,{1,8,16,24},{1,8,15,22}),{1,2,3,4})
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Divide month into four parts

Post by YasserKhalil »

Than\t's wonderful and awesome Mr. Hans
Thank you very much

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Divide month into four parts

Post by HansV »

Slightly shorter:

=MATCH(DAY(A1),SWITCH(DAY(EOMONTH(A1,0)),30,{1,8,15,23},31,{1,8,16,24},{1,8,15,22}))
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Divide month into four parts

Post by YasserKhalil »

Thanks a lot for all your gifts