Adding if statement to arrayformula

[cpansewicz]

Hi,

I have a formula that pulls two columns of time together into a third column. The formula is in Column S and is:

=arrayformula(
lower(TEXT(Q2,"h:mm AM/PM")&" to "&IF(R2="","",TEXT(R2,"h:mm AM/PM"))))

(for example if both columns are not blank, this generates "6:00 pm to 8:00 pm")

What I would like to happen is in the instance that Col Q = "12:00 PM", then the formula would generate for instance "Noon to 8:00 pm".

Thank you,
Camille

 

[Scott Huish]

I had to look it up. ARRAYFORMULA is a Google Sheets function, not an Excel function. Although this isn't an array formula at all, not sure why you're using that.

In Excel, I would do it like this, but I don't know how it will translate to Google Sheets (This is not a Google forum)

=SUBSTITUTE(LOWER(TEXT(Q2,"h:mm AM/PM") & " to " & IF(R2="","",TEXT(R2,"h:mm AM/PM"))),"12:00 pm","Noon")

 

[cpansewicz]

Hi,

Thank you so much! That is exactly what I was looking for. Yes, sorry I included the "arrayformula" portion. That is from google sheets. It adds the functionality of copying down a formula throughout the sheet.