This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
| Invoice.ID | Upcoming Due Date(Results) | ||||
|---|---|---|---|---|---|
| AA-001 | 15-09-2024 | ||||
| BB-001 | 30-09-2024 | ||||
| AA-002 | 15-09-2024 | ||||
| CC-001 | due dates lapsed | ||||
| DD-001 | 30-11-2024 | ||||
| BB-003 | 30-09-2024 | ||||
| DD-002 | 30-11-2024 | ||||
| CC-002 | due dates lapsed | ||||
| EE-001 | 30-09-2024 | ||||
| EE-002 | 30-09-2024 |
Based on today's date all upcoming due dates be generated and all dates are to be in dd-mm-yyyy format. As in cell B1 Inv.Id starts with AA corresponding fortnight ends on 15-09-2024 as per today' date. The moment current date changes to 16-09-2024 upcoming date be changed to end fortnight date of current month i.e to 30-09-2024. What formula will yield results as given in column C.
Condition helper Table:(only for conditions explanation)
| invoice ids category | due date periods | |
|---|---|---|
| invoice id starts with AA | Fortnightly | |
| " " BB | Monthly | |
| "" "" CC | JAN, JUL | |
| "" "" DD | FEB,MAY,AUG,,NOV | |
| "" "" EE | MAR,JUN,SEP,DEC |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
In C2:
=SWITCH(LEFT(B2, 2),
"AA", IF(DAY(TODAY())<16, DATE(YEAR(TODAY()), MONTH(TODAY()), 15), EOMONTH(TODAY(), 0)),
"BB", EOMONTH(TODAY(), 0),
"CC", EOMONTH(TODAY(), 5-MOD(MONTH(TODAY())+4, 6)),
"DD", EOMONTH(TODAY(), 2-MOD(MONTH(TODAY()), 3)),
"EE", EOMONTH(TODAY(), 2-MOD(MONTH(TODAY())-1, 3)))
Thank You HansV, Million thanks ,trying to understand the logical calculation you have used in this function. Million thanks once again.