Work around leap year in formula and display appropriate dates (if?)

D

dnelley

New Member
Joined
Apr 25, 2014
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I would like a formula that could generate the following outputs


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[TD]AK[/TD]
[TD]AL[/TD]
[TD]AM[/TD]
[TD]AN[/TD]
[TD]AO[/TD]
[/TR]
[TR]
[TD]ex:1[/TD]
[TD][/TD]
[TD]2/1/14[/TD]
[TD]2/8/14[/TD]
[TD]2/15/14[/TD]
[TD]2/22/14[/TD]
[TD][/TD]
[TD]3/1/14[/TD]
[TD]3/8/14[/TD]
[TD]3/15/14[/TD]
[TD]3/22/14[/TD]
[TD]3/29/14[/TD]
[TD]4/1/14[/TD]
[TD]4/8/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ex:2[/TD]
[TD][/TD]
[TD]2/1/16[/TD]
[TD]2/8/16[/TD]
[TD]2/15/16[/TD]
[TD]2/22/16[/TD]
[TD]2/29/16[/TD]
[TD]3/1/16[/TD]
[TD]3/8/16[/TD]
[TD]3/15/16[/TD]
[TD]3/22/16[/TD]
[TD]3/29/16[/TD]
[TD]4/1/16[/TD]
[TD]4/8/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am having a hard time creating a formula the would produce the above

I have tried to create the above using nested if statements -- but after a few cells my formula no longer gives valid results

here's what I had tried to use:
IF(AC158=FALSE, EOMONTH(AB158,0)+1, IF(MONTH(AC158+7)=MONTH(AC158), AC158+7, IF(((EOMONTH(AB158,-1)+1)+(4*7))=(AC158+7), EOMONTH(AB158,0)+1, FALSE))) == cell AD158
the formula is dragged to the right

this one almost works but i get too many or too few empty("FALSE") cells than I want

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[TD]AK[/TD]
[TD]AL[/TD]
[TD]AM[/TD]
[TD]AN[/TD]
[TD]AO[/TD]
[/TR]
[TR]
[TD]ex:1[/TD]
[TD]158[/TD]
[TD][/TD]
[TD]2/1/14
[/TD]
[TD]2/8/14[/TD]
[TD]2/15/14[/TD]
[TD]2/22/14[/TD]
[TD]3/1/14[/TD]
[TD]3/8/14[/TD]
[TD]3/15/14[/TD]
[TD]3/22/14[/TD]
[TD]3/29/14[/TD]
[TD][/TD]
[TD]4/1/16[/TD]
[TD]4/8/14[/TD]
[TD]4/15/16[/TD]
[/TR]
[TR]
[TD]ex:2[/TD]
[TD]158[/TD]
[TD][/TD]
[TD]2/1/16
[/TD]
[TD]2/8/16[/TD]
[TD]2/15/16[/TD]
[TD]2/22/16[/TD]
[TD]2/29/16[/TD]
[TD][/TD]
[TD]3/1/16[/TD]
[TD]3/8/16[/TD]
[TD]3/15/16[/TD]
[TD]3/22/16[/TD]
[TD]3/29/16[/TD]
[TD][/TD]
[TD]4/1/16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


here's another one
IF(MONTH(AC174+7)=MONTH(AC174), AC174+7, IF(((EOMONTH(AB174,-1)+1)+(4*7))=(AC174+7), EOMONTH(AB174,0)+1, IF(AC174=FALSE, EOMONTH(AB174,0)+1, FALSE))) == cell AD174



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[TD]AK[/TD]
[TD]AL[/TD]
[TD]AM[/TD]
[TD]AN[/TD]
[TD]AO[/TD]
[/TR]
[TR]
[TD]ex:1[/TD]
[TD]174[/TD]
[TD][/TD]
[TD]2/1/14
[/TD]
[TD]2/8/14[/TD]
[TD]2/15/14[/TD]
[TD]2/22/14[/TD]
[TD]3/1/14[/TD]
[TD]3/8/14[/TD]
[TD]3/15/14[/TD]
[TD]3/22/14[/TD]
[TD]3/29/14[/TD]
[TD][/TD]
[TD]1/7/00[/TD]
[TD]1/14/00[/TD]
[TD]1/21/00[/TD]
[/TR]
[TR]
[TD]ex:2[/TD]
[TD]174[/TD]
[TD][/TD]
[TD]2/1/16
[/TD]
[TD]2/8/16[/TD]
[TD]2/15/16[/TD]
[TD]2/22/16[/TD]
[TD]2/29/16[/TD]
[TD][/TD]
[TD]1/7/00[/TD]
[TD]1/14/00[/TD]
[TD]1/28/00[/TD]
[TD]#num[/TD]
[TD]#num[/TD]
[TD]#num[/TD]
[TD]#num[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


another fail:
IF(AC135=FALSE, (EOMONTH(AB135,0))+1, IF(MONTH(AC135+7)=MONTH(AC135), AC135+7, FALSE))== cell AD174



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[TD]AK[/TD]
[TD]AL[/TD]
[TD]AM[/TD]
[TD]AN[/TD]
[TD]AO[/TD]
[/TR]
[TR]
[TD]ex:1[/TD]
[TD]135[/TD]
[TD][/TD]
[TD]2/1/14
[/TD]
[TD]2/8/14[/TD]
[TD]2/15/14[/TD]
[TD]2/22/14[/TD]
[TD][/TD]
[TD]3/1/14[/TD]
[TD]3/8/14[/TD]
[TD]3/15/14[/TD]
[TD]3/22/14[/TD]
[TD]3/29/14[/TD]
[TD][/TD]
[TD]4/1/14[/TD]
[TD]4/8/14[/TD]
[/TR]
[TR]
[TD]ex:2[/TD]
[TD]135[/TD]
[TD][/TD]
[TD]2/1/16
[/TD]
[TD]2/8/16[/TD]
[TD]2/15/16[/TD]
[TD]2/22/16[/TD]
[TD]2/29/16[/TD]
[TD][/TD]
[TD]3/1/16[/TD]
[TD]3/8/16[/TD]
[TD]3/15/16[/TD]
[TD]3/22/16[/TD]
[TD]3/29/16[/TD]
[TD][/TD]
[TD]4/1/16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I'm trying to display five weeks for every month and since february doesn't consistently have five weeks I need my formula to recognize this,
ultimately i would like to generate a gantt table around these outputs

any insight/assistance is welcomed


Thanks :Dee
 
Sort by date Sort by votes
Hi,

I am not sure what you are trying to do. No month has a complete five weeks. If it did there could be 60 weeks in a year and not 52 and a bit.

Why do you want to have some dates 7 days apart and some only 1 day apart?
 
Upvote 0
Hello Dee,

I used this formula for AD2

=IF(AC2=FALSE,EOMONTH(AB2,0)+1,IF(MONTH(AC2+7)=MONTH(AC2),AC2+7,IF(DAY(AC2)=22,FALSE,EOMONTH(AC2,0)+1)))

does that work for you?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
391
etaf
etaf
D
  • Solved
List of items within a specific month
Replies
4
Views
544
Fluff
Fluff
V
  • Question Question
Adjust formula to calculate monthly dynamic average of work hours
Replies
2
Views
62
vladimiratanasiu
V
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
534
arendberg
A
E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
175
JEC
JEC

Forum statistics

Threads
1,226,837
Messages
6,193,251
Members
453,784
Latest member
Chandni

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top