Forum Discussion

Copper Contributor

Aug 22, 2022

need help with a formula

I have a excel sheet that has an employee's Provident fun deduction amount per month from the starting date Now I need to bring “from which month and year an employee has started to deduct his Prov...

PF.xlsx12 KB

Excel

Formulas and Functions

HansVogelaar
Aug 23, 2022
SKA217

The formula that I posted will only work in Excel 365 / 2021. The following version should work in older versions too:

=INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) & ", " & INDEX($D$1:$AM$1,QUOTIENT(MATCH(TRUE,D3:AM3>0,0)-1,12)*12+1)

Confirm with Ctrl+Shift+Enter.

SKA217

Copper Contributor

attached the excel file. please help me bring the starting date with formula for each employee

Harun24HR

Bronze Contributor

Aug 22, 2022

And what result do you expect?

SKA217
Copper Contributor
Aug 22, 2022
I need the starting date (from when the employee is deduction provident fund amount)
example: Hemayet uddin from Jan, 2015
Md Moniruzzaman (Monir) from September, 2015
- HansVogelaar
  MVP
  Aug 22, 2022
  SKA217
  
  In a cell in row 3:
  
  =LET(i, MATCH(TRUE,D3:AM3>0,0), m, INDEX($D$2:$AM$2,i), y, INDEX($D$1:$AM$1,QUOTIENT(i-1,12)*12+1), m &", "&y)
  
  Fill down.
  - SKA217
    Copper Contributor
    Aug 23, 2022
    its not working. it shows #NAME?