I am trying to set a formula so that when I put a number in (eg. 20190717) it will switch to 2019-07-17 o it is easier to read.
So what I am downloading is a CV file which I copy and put in my Numbers. And I would like some of the columns to change to this format.
Is this possible?
Hi nicole,
You can easily do this with a formula in an added column.
My formula in D3=LEFT(C2,4)&"-"&MID(C2,5,2)&"-"&RIGHT(C2,2)
You will want to copy the result column and then Paste Formula Results. This will give you the text and the freedom to delete the original column. If you paste them into a column already formatted as Date/Time you will be able to use them in other calculations. If it is only so you can read them better the text format will serve you fine.
quinn
So Quinn, I keep getting an error Message:
MID requires 3 arguments, but was given 1
My formula is the same as yours except it keeps putting an extra ")" at the end.
LEFT(H4,4)&"-"&MID((H4,5,2)&"-"&RIGHT(H4,2))
Works fine for me after correcting your redundant set of parenthesis.
Copy the formula below and paste it into your document in row 4 so it is exact.
LEFT(H4,4)&"-"&MID(H4,5,2)&"-"&RIGHT(H4,2)
EDIT: I was too slow, glad you got it fixed.
Hi nicole,
You have
&"-"&RIGHT(C5,2)
tucked inside the MID function. Click on MID to see what I mean.
You could simply redo it.
or:
Cut
&"-"&RIGHT(C5,2)
out of your formula, delete the extra parenthesis, and paste
&"-"&RIGHT(C5,2)
back after MID.
quinn
Thanks Quinn. I will give this a try.
Opps, I see i have an extra parenthesis after MID which causes the extra parenthesis at the end.
I fixed that and I still get the same error message.
Figured it out. No syntax or MID errors.
Thanks Quin for your help
Great, Nicole,
Glad to help.
quinn
formulas for Dates
