Find lastest date in row and detail which quarter it falls in

[Godders199]

Hello, I am building a new spreadsheet and need to find the last date in a row and evidence which Quarter it falls in as per this example below.

date 1	date 2	date3	Quarter
01/01/2018	01/06/2018	01/03/2018	Q2
01/03/2018	01/04/2018	01/07/2018	Q3

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="3"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

I now using =max(a1:a3) will give me th latest date ,but i dont know how to convert that to a quarter.

Also i am looking to convert his to vba, as i am trying to avoid any formulas in worksheet, to keep it functioning efficiently.

Hope someone can hel.

 

[njimack]

The formula would be:

="Q"&ROUNDUP(MONTH(MAX(A2:C2))/3,0)

The VBA would be:

Range("D2").FormulaR1C1 = "=""Q""&ROUNDUP(MONTH(MAX(RC[-3]:RC[-1]))/3,0)"

 

[Godders199]

Thanks for the formula.

Would that VBA just put the formula in cell d2?which i trying to avoid ,just really want to see the result. Iwill give it ago and see what impact it has on the workbook.

 

[njimack]

    With Range("D2")
        .FormulaR1C1 = "=""Q""&ROUNDUP(MONTH(MAX(RC[-3]:RC[-1]))/3,0)"
        .Formula = .Value
    End With