I'm working with a sales pipeline sheet and need a formula to generate the project start and end months based on invoice charges inputted in month columns. For example if a client delays a project and invoice months change, I'd ideally like the Project start and end month to update automatically as I update invoice months.

For example if I was to move 2000 and 1000 on the table below (row 1) to the Apr & May columns respectively, the formula would update Project start to Apr, and Project end to May.

CodePudding user response：

I would advise you to use the following cell formatting for the dates of your project (cell formatting, Custom):

mmm

In order to get to the next month, you can use the following formula:

=MONTH(DATEVALUE(DAY(B8)&"/"&MONTH(B8) 1&"/"&YEAR(B8)))

This takes the day, the month plus one and the year, and converts this into a new date from which the cell formatting will just show you the month. Be careful: this only works while working within the same year. In case you jump from December to January the next year, you need an IF() function to make this work.

CodePudding user response：

You can use these formulas for the start and end date - if you have Excel 365

=INDEX($A$1:$G$1, MIN(FILTER(COLUMN(D2:G2),D2:G2<>"")))

=INDEX($A$1:$G$1, MAX(FILTER(COLUMN(D2:G2),D2:G2<>"")))

• COLUMN(D2:G2) returns an array with the column indices of the invoice amounts.
• FILTER only returns those column indices that have an amount
• and finally MIN and MAX return the first and last column index
• this is then passed to INDEX on the header row.