CODEWITHSUNDEEP
excelexcel-formula
Chris H.
Chris H.

Reputation: 325

Excel formula for entering bi-weekly dates

I have the following worksheet:

The grid is filled with the following formula (this example is from cell H4) that populates the grid based on inputs from the table on the left, =IF($A4="","",IF(AND($E4="Daily",H$2>=$D4,H$2<=$G4),IF(RIGHT($F4,2)="30",LEFT($F4,LEN($F4)-2)&"/",IF(RIGHT($F4,2)="00",LEFT($F4,LEN($F4)-2),$F4)),IF(AND($E4="Weekly",H$2>=$D4,H$2<=$G4,TEXT(H$2,"DDD")=TEXT($D4,"DDD")),IF(RIGHT($F4,2)="30",LEFT($F4,LEN($F4)-2)&"/",IF(RIGHT($F4,2)="00",LEFT($F4,LEN($F4)-2),$F4)),IF(AND($E4="Bi-Weekly",H$2>=$D4,H$2<=$G4,MOD($D4+14,H$2)=0),IF(RIGHT($F4,2)="30",LEFT($F4,LEN($F4)-2)&"/",IF(RIGHT($F4,2)="00",LEFT($F4,LEN($F4)-2),$F4)),IF(AND($E4="Monthly",H$2>=$D4,H$2<=$G4,TEXT(H$2,"MM/DD/YYYY")=CONCATENATE(TEXT(H$2,"MM"),"/",TEXT($D4,"DD"),"/",TEXT($D4,"YYYY"))),IF(RIGHT($F4,2)="30",LEFT($F4,LEN($F4)-2)&"/",IF(RIGHT($F4,2)="00",LEFT($F4,LEN($F4)-2),$F4)),IF(COUNTIF('PowerPoint Gantt'!$A$5:$A$12,$A4)=1,IF(H$2=VLOOKUP($A4,'PowerPoint Gantt'!$A$5:$E$12,5,FALSE)+31,"R",""),""))))))

The only part of the function that isn't working is the Bi-Weekly selection. I can't figure out how to get recurring entries. I can get the start date and one 14 day period after. I've tried using the CEILING function also but still only gets me the next 14th day marked, instead of every 14th day. And ideas?

Upvotes: 1

Views: 540

Answers (1)

Reinier Torenbeek
Reinier Torenbeek

Reputation: 17383

In your rule for Bi-Weekly meetings, it seems that

MOD($D4+14,H$2)=0

should be replaced with

MOD(H$2-$D4,14)=0

The latter takes the difference between the starting date and the actual date and checks to see if that can be divided by 14, the number of days in 2 weeks.

Your rule for Weekly meetings could be approached similarly, which seems simpler to me than a rule based on the name of the day, like you are using now.

Upvotes: 3

Related Questions