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.

[TABLE="width: 216"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="3"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 75, bgcolor: transparent"]date 1[/TD]
[TD="width: 75, bgcolor: transparent"]date 2[/TD]
[TD="width: 75, bgcolor: transparent"]date3[/TD]
[TD="width: 64, bgcolor: transparent"]Quarter[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/01/2018[/TD]
[TD="bgcolor: transparent, align: right"]01/06/2018[/TD]
[TD="bgcolor: transparent, align: right"]01/03/2018[/TD]
[TD="bgcolor: transparent"]Q2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/03/2018[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2018[/TD]
[TD="bgcolor: transparent, align: right"]01/07/2018[/TD]
[TD="bgcolor: transparent"]Q3[/TD]
[/TR]
</tbody>[/TABLE]

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