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

Date	¼Month in days
1/1/2019	7.75
2/1/2019	7.00
3/1/2019	7.75
4/1/2019	7.50
5/1/2019	7.75
6/1/2019	7.50
7/1/2019	7.75
8/1/2019	7.75
9/1/2019	7.50
10/1/2019	7.75
11/1/2019	7.50
12/1/2019	7.75

<tbody>
</tbody>

 

[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:

Date	¼Month in days	Start Part1	Start Part2	Start Part3	Start Part4	End Part4
January	7.75	1/1/19 0:00	1/8/19 18:00	1/16/19 12:00	1/24/19 6:00	1/31/19 23:59
February	7	2/1/19 0:00	2/8/19 0:00	2/15/19 0:00	2/22/19 0:00	2/28/19 23:59
March	7.75	3/1/19 0:00	3/8/19 18:00	3/16/19 12:00	3/24/19 6:00	3/31/19 23:59
April	7.5	4/1/19 0:00	4/8/19 12:00	4/16/19 0:00	4/23/19 12:00	4/30/19 23:59
May	7.75	5/1/19 0:00	5/8/19 18:00	5/16/19 12:00	5/24/19 6:00	5/31/19 23:59
June	7.5	6/1/19 0:00	6/8/19 12:00	6/16/19 0:00	6/23/19 12:00	6/30/19 23:59
July	7.75	7/1/19 0:00	7/8/19 18:00	7/16/19 12:00	7/24/19 6:00	7/31/19 23:59
August	7.75	8/1/19 0:00	8/8/19 18:00	8/16/19 12:00	8/24/19 6:00	8/31/19 23:59
September	7.5	9/1/19 0:00	9/8/19 12:00	9/16/19 0:00	9/23/19 12:00	9/30/19 23:59
October	7.75	10/1/19 0:00	10/8/19 18:00	10/16/19 12:00	10/24/19 6:00	10/31/19 23:59
November	7.5	11/1/19 0:00	11/8/19 12:00	11/16/19 0:00	11/23/19 12:00	11/30/19 23:59
December	7.75	12/1/19 0:00	12/8/19 18:00	12/16/19 12:00	12/24/19 6:00	12/31/19 23:59

<tbody>
</tbody>

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.