SOLVED

Formula for salary budget - month salary starts by drop-down list

Copper Contributor

Dear Community,

 

I am looking for a formula that completes cells with a salary budget calendar from the month of employment onwards: Employment start column cell (now March) is a drop-down list. When this month changes, the cell with the respective months forward should change (now March, April, May, June), and all previous months to be 0 (like in January, February). 

 

Thank you for your advice in advance. 

 

  Q1Q2
 Salary  Employment start JanuaryFebruaryMarchAprilMayJune
        
                10 000,00 Kč March                                -   Kč                     -   Kč      10 000,00 Kč      10 000,00 Kč      10 000,00 Kč  10 000,00 Kč
6 Replies

HI @janhouskaRMHC 

 

You may achieve your requirement using below formula:

=IF((C2>=$B$4),$A$4,0)

 

tauqeeracma_0-1639130398110.png

 

You may also refer to the attached file for more clarity.

Please let me know if it works for you.

 

Thanks

Tauqeer

Dear @tauqeeracma,

 

Thank you very much for your kind assistance. I already solved this riddle by IF formula, but your help is appreciated.  :thumbs_up:

 

All the best!

 

Jan

@janhouskaRMHC 

For anyone with Excel 365 this reduces to a formula for the displayed dates and a single spilt range formula for employees 

= EOMONTH(displayStart, SEQUENCE(1,12,0))

= IF(displayDate>=employmentStart, Salary, "")

image.png

For those without 365 (or Excel 2021), bad luck!  My sympathies.

Hi Peter, thank you very much for your response. I am the one with Excel 2021, but I appreciate your help anyway. Best, Jan
best response confirmed by janhouskaRMHC (Copper Contributor)
Solution

@janhouskaRMHC 

If you do have Excel 2021, the attached file should work.  Formulae are to be found in the blue shaded cells and the two columns to the left are an Excel Table

image.png

Thank you very much, @Peter Bartholomew, this looks great.
1 best response

Accepted Solutions
best response confirmed by janhouskaRMHC (Copper Contributor)
Solution

@janhouskaRMHC 

If you do have Excel 2021, the attached file should work.  Formulae are to be found in the blue shaded cells and the two columns to the left are an Excel Table

image.png

View solution in original post