Excel Formula to generate month Date of project

haydenwhitehouse 0
Viewed 24

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.

Project start Project end Jan Feb Mar Apr May
Jan Feb 2000 1000
Jan April 1000 1000 1000 1000
[forumula to generate feb?] [forumla to generate May?] 500 500 500 500 500
excel-formula excel
2 Answers

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.

Dominique0

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.

enter image description here

useryPy710
Related