Divide Month Into Four Equal Parts

[wsnyder]

Hi all,

Using Excel 2016.

How can I divide any given months into 4 equal parts so I can determine if a task was complete in part1, part2, part3 or part4?
I tried dividing 3/31/2019 into 4 equal parts of 7.75 hours each, but I don't like the way this is heading.
There has to be an easier way that is flexible to handle 28, 29, 30, or 31 depending on the month and year.

thanks,
-lou

 

[Rick Rothstein]

How can I divide any given months into 4 equal parts so I can determine if a task was complete in part1, part2, part3 or part4?
I tried dividing 3/31/2019 into 4 equal parts of 7.75 hours each, but I don't like the way this is heading.
There has to be an easier way that is flexible to handle 28, 29, 30, or 31 depending on the month and year.

Give us an example... what would the four equal parts of March 2019 look like?

 

[GR00007]

Will this help? The formula used for January was copied down thru December

=((EOMONTH(A2,0) - A2) + 1) / 4

[TABLE="class: grid, width: 173"]
<tbody>[TR]
[TD]Date[/TD]
[TD]¼Month in days[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]7.00[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2019[/TD]
[TD="align: right"]7.50[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2019[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2019[/TD]
[TD="align: right"]7.50[/TD]
[/TR]
[TR]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2019[/TD]
[TD="align: right"]7.50[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2019[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2019[/TD]
[TD="align: right"]7.50[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2019[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
</tbody>[/TABLE]

 

[wsnyder]

Thanks Rick,


In the example of March, 2019 (31/4 = 7.75 days in each part)


Part 1 : 3/1, 3/2, 3/3, 3/4, 3/5, 3/6, 3/7, 3/8 up to 6pm
Part 2 : 3/8 18:00:01, 3/9, 3/10, 3/11, 3/12, 3/13, 3/14, 3/15, 3/16 up to 12pm
Part 3 : 3/16 12:00:01, 3/17, 3/18, 3/19, 3/20, 3/21, 3/22, 3/23, 3/24 up to 6am
Part 4 : 3/24 06:00:01, 3/25, 3/26, 3/27, 3/28, 3/29, 3/30, 3/31

thanks,
-lou

 

[GR00007]

Here's the break out for 12 months:
[TABLE="class: grid, width: 602"]
<tbody>[TR]
[TD]Date[/TD]
[TD]¼Month in days[/TD]
[TD]Start Part1[/TD]
[TD]Start Part2[/TD]
[TD]Start Part3[/TD]
[TD]Start Part4[/TD]
[TD]End Part4[/TD]
[/TR]
[TR]
[TD="align: right"]January[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]1/1/19 0:00[/TD]
[TD="align: right"]1/8/19 18:00[/TD]
[TD="align: right"]1/16/19 12:00[/TD]
[TD="align: right"]1/24/19 6:00[/TD]
[TD="align: right"]1/31/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]February[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2/1/19 0:00[/TD]
[TD="align: right"]2/8/19 0:00[/TD]
[TD="align: right"]2/15/19 0:00[/TD]
[TD="align: right"]2/22/19 0:00[/TD]
[TD="align: right"]2/28/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]March[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]3/1/19 0:00[/TD]
[TD="align: right"]3/8/19 18:00[/TD]
[TD="align: right"]3/16/19 12:00[/TD]
[TD="align: right"]3/24/19 6:00[/TD]
[TD="align: right"]3/31/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]April[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]4/1/19 0:00[/TD]
[TD="align: right"]4/8/19 12:00[/TD]
[TD="align: right"]4/16/19 0:00[/TD]
[TD="align: right"]4/23/19 12:00[/TD]
[TD="align: right"]4/30/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]May[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]5/1/19 0:00[/TD]
[TD="align: right"]5/8/19 18:00[/TD]
[TD="align: right"]5/16/19 12:00[/TD]
[TD="align: right"]5/24/19 6:00[/TD]
[TD="align: right"]5/31/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]June[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]6/1/19 0:00[/TD]
[TD="align: right"]6/8/19 12:00[/TD]
[TD="align: right"]6/16/19 0:00[/TD]
[TD="align: right"]6/23/19 12:00[/TD]
[TD="align: right"]6/30/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]July[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]7/1/19 0:00[/TD]
[TD="align: right"]7/8/19 18:00[/TD]
[TD="align: right"]7/16/19 12:00[/TD]
[TD="align: right"]7/24/19 6:00[/TD]
[TD="align: right"]7/31/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]August[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]8/1/19 0:00[/TD]
[TD="align: right"]8/8/19 18:00[/TD]
[TD="align: right"]8/16/19 12:00[/TD]
[TD="align: right"]8/24/19 6:00[/TD]
[TD="align: right"]8/31/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]September[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9/1/19 0:00[/TD]
[TD="align: right"]9/8/19 12:00[/TD]
[TD="align: right"]9/16/19 0:00[/TD]
[TD="align: right"]9/23/19 12:00[/TD]
[TD="align: right"]9/30/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]October[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]10/1/19 0:00[/TD]
[TD="align: right"]10/8/19 18:00[/TD]
[TD="align: right"]10/16/19 12:00[/TD]
[TD="align: right"]10/24/19 6:00[/TD]
[TD="align: right"]10/31/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]November[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]11/1/19 0:00[/TD]
[TD="align: right"]11/8/19 12:00[/TD]
[TD="align: right"]11/16/19 0:00[/TD]
[TD="align: right"]11/23/19 12:00[/TD]
[TD="align: right"]11/30/19 23:59[/TD]
[/TR]
[TR]
[TD="align: right"]December[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]12/1/19 0:00[/TD]
[TD="align: right"]12/8/19 18:00[/TD]
[TD="align: right"]12/16/19 12:00[/TD]
[TD="align: right"]12/24/19 6:00[/TD]
[TD="align: right"]12/31/19 23:59[/TD]
[/TR]
</tbody>[/TABLE]
Column A (Month) is the first day of the month (formatted mmmm)
B2:

[COLOR=#574123]=((EOMONTH(A2,0) - A2) + 1) / 4[/COLOR]

C2:

=A2

D2:

=A2 + B2

E2:

=A2 + (B2 *2)

F2:

=A2 + (B2 *3)

G2:

=EOMONTH(A2,0) +0.9993

 

[wsnyder]

Thanks GR00007,

Your solution is much better than the direction I was heading - thanks.
A small issue to resolve is February with 29 days in a leap year.

Thanks,
-lou

 

[GR00007]

Actually that formula in B2 will accommodate February 29.