How to format dates.
- Thread starter Eloise T
- Start date
Laura Pankey
New Member
Click and highlight all the cells you want to format, go to the NUMBERS block on the Home tab, click the down arrow until you get the dialog box. Click "CUSTOM" then you can type the format into the blank box at the top under TYPE:. I usually like my dates to look formal (i.e. 01/01/2016). to get this, type mm/dd/yyyy. You can use this for any format. If you want to delete the "0" in from of the month and day and want two digit year then type m/d/yy. There are also examples you can choose from if you scroll down the list. It has dates with times, dates with day of the week, etc.
Eloise T
Active Member
...Format the dates in column G so they all look like G1-G4 and G25-G28 and not like column H?
Marc L
Excel Ninja
Hi Eloïse !
When a date is a real one (as number, not text like G5-G24),
you just have to format cell back to General …
If you test on an empty column, no issue …
But your column H has one, so the question how dates were populated ?
Anyway, for both columns G & H, jindon already gave you the path !
When a date is a real one (as number, not text like G5-G24),
you just have to format cell back to General …
If you test on an empty column, no issue …
But your column H has one, so the question how dates were populated ?
Anyway, for both columns G & H, jindon already gave you the path !
Eloise T
Active Member
Eloise T
Active Member
Please check the attachment as some worked and some didn't.
Eloise T
Active Member
Please see my reply to jindon. Thanks.
Eloise T
Active Member
...Format the dates in column G so they all look like G1-G4 and G25-G28 and not like column H?
Eloise T
Active Member
Marc L
Excel Ninja
One issue !
Eloise T
Active Member
THANK YOU!
Eloise T
Active Member
In other words, force the text data to numerical data?
Converting the data into numerical format will give the same result as u had posted.You will have to multiply/divide it by 1.
Eloise T
Active Member
I spoke too soon. Your formula changed the dates to numerical numbers (good), but it seemed to nullify the elimination of the N/A text which was the original formula. Any suggestions?
I'm not good at formulas, so not really sure if this is right...
=IFERROR(INT(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),)),DATE(RIGHT(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),),4),LEFT(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),),2),MID(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),),4,2)))
=IFERROR(INT(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),)),DATE(RIGHT(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),),4),LEFT(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),),2),MID(INDEX(H1:H10,MIN(NOT(ISERROR(H1:H10)),ROW(H1:H10)),),4,2)))
G1:
=IFERROR(INT(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),)),DATE(RIGHT(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),),4),LEFT(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),),2),MID(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),),4,2)))
Confirm with Ctrl + Shift + Enter (Array formula entry)
then copy down...
Hope this works
=IFERROR(INT(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),)),DATE(RIGHT(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),),4),LEFT(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),),2),MID(INDEX(H$1:H$21,MIN(IF(NOT(ISERROR(H1:H$21)),ROW(H1:H$21))),),4,2)))
Confirm with Ctrl + Shift + Enter (Array formula entry)
then copy down...
Hope this works
Eloise T
Active Member
See attachment with updated formula. It appears to be the same result. The N/As are not resolved. I appreciate your efforts.
So, it is not really an Error, just "N/A"...
=IFERROR(INT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),)),DATE(RIGHT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),4),LEFT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),2),MID(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),4,2)))
And you must confirm with Ctrl+Shift+Enter before you copy down.
=IFERROR(INT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),)),DATE(RIGHT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),4),LEFT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),2),MID(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),4,2)))
And you must confirm with Ctrl+Shift+Enter before you copy down.
Chihiro
Excel Ninja
Something like below?
=IF(G2="N/A","N/A",IF(ISNUMBER(G2),G2,VALUE(RIGHT(G2,4)&"/"&LEFT(G2,2)&"/"&MID(G2,4,2))))
Since I'm using Japanese regional setting on the copy of Excel I'm working on, you may need to change below portion of the formula.
VALUE(RIGHT(G2,4)&"/"&LEFT(G2,2)&"/"&MID(G2,4,2))
To
VALUE(LEFT(G2,2)&"/"&MID(G2,4,2)&"/"&RIGHT(G2,4))
Edit: Unified cell reference.
=IF(G2="N/A","N/A",IF(ISNUMBER(G2),G2,VALUE(RIGHT(G2,4)&"/"&LEFT(G2,2)&"/"&MID(G2,4,2))))
Since I'm using Japanese regional setting on the copy of Excel I'm working on, you may need to change below portion of the formula.
VALUE(RIGHT(G2,4)&"/"&LEFT(G2,2)&"/"&MID(G2,4,2))
To
VALUE(LEFT(G2,2)&"/"&MID(G2,4,2)&"/"&RIGHT(G2,4))
Edit: Unified cell reference.
Last edited: