Calculation based on the day of the year, not the date, for projecting results

[DianeP]

I am trying to project the year's performance based on how we are doing so far in the year.

In my example, Column H is a projection of Column B. Col. I is a projection of Col. D. The formulas in Columns H and I are based on the date in cell B19. That date is the 278th day of the year, so my Column H & I formulas divide the contents of Columns B & D by 278, then multiply them by 365 to get the full year projections needed for columns H & I.

Next month when I enter new data into columns B & D, I will enter the date that I do so in cell B19. Can I get the formulas for columns H & I to automatically change the 278th day of the year to whatever day of the year the date in B19 is?

[daffodil11]

Sure, just substitute references of 278 with TEXT(B19-DATE(YEAR(B19),1,0),"000").

[protonLeah]

Alternatively, if you want automatic current date entry rather than manual entry, use:

Please Login or Register  to view this content.

Put the formula in H3 and format H3 as Custom using: 0 "Day Projection" and modify the formula in H5, etc. to =(B5/$H$3)*365

[DianeP]

Thanks for the input. Although I don't need that calculation at this time, it may prove valuable in the future. I appreciate your help.