Format Date and Time

0
votes

In column A I have date and time. In Column B I have this formula to split them:

={"COL B","COL C"; ArrayFormula(IF(NOT(ISBLANK(A2:A)), SPLIT(A2:A, " ")))}

My sheet is linked to a Google form. Every time a new sheet is submitted, the new data looks like 44075 and 0.9756944444 and I always need to format as Date and the other one as time.

Is there any way that my formula will automatically format the splitted value in col b and c?

2
google-sheetssplitgoogle-sheets-formulaarray-formulas

2 Answers

0
votes

You might try this:

={"Date","Time"; ArrayFormula(IF(ISBLANK(A2:A),, {DATEVALUE(A2:A),TIMEVALUE(A2:A)}))}

or this:

 ={"Date","Time"; ArrayFormula(IF(ISBLANK(A2:A),, {INT(A2:A),MOD(A2:A,1)}))}

In either case, you'll want to do Format>Number Date for B and Format>Number>Time for C.

UPDATE: Since you seem to want the formatting to appear a certain way without using the Format>Number option, this will output dates and times...

=ARRAYFORMULA({"Date","Time";IF(A2:A="",,TEXT(A2:A,{"m/d/yy","h:mm am/pm"}))})
0
votes

I've tried a lot of formula and surrendered. I've just added a script:

var date = ss.getRange('R2:R'); date.setNumberFormat("MM/dd/yyyy");

var Time = ss.getRange('S2:S');
Time.setNumberFormat("hh:mm:ss AM/PM");