# Aargh!  Can anyone help me custom format this numeral in Excel?

**URL:** <https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493>\
**Category:** Factual Questions\
**Created:** [August 7, 2003, 1:56am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493 "2003-08-07T01:56:20Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![rexnervous](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rexnervous](https://boards.straightdope.com/u/rexnervous)\
**Post date:** [August 7, 2003, 1:56am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/1 "2003-08-07T01:56:20Z")

</div>

Should be easy, but I can’t figure it out.

I have one column of data that looks like this:

20030125115000

This is non-parsed date/time data; in other words, this numeral marks 11:50:00 AM on January 25, 2003. (hours are military time, so there’s no ‘marker’ for AM v. PM).

I’ve tried various ways of customizing format, but I guess I don’t understand how the tool works.

Anyone know?

I don’t care how the output looks per se, just want it readable.

---

<div class="post-metadata">

**Author:** ![rexnervous](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rexnervous](https://boards.straightdope.com/u/rexnervous)\
**Post date:** [August 7, 2003, 1:59am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/2 "2003-08-07T01:59:53Z")

</div>

I should add that I don’t care about the time, so if there’s a way to just ‘delete’ the last six digits and only display date that’d be even better.

---

<div class="post-metadata">

**Author:** ![starfish](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@starfish](https://boards.straightdope.com/u/starfish)\
**Post date:** [August 7, 2003, 2:09am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/3 "2003-08-07T02:09:49Z")

</div>

goto format, cell, type in:  
yyyymdhms

If you don’t want the time, leave off hms

---

<div class="post-metadata">

**Author:** ![rexnervous](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rexnervous](https://boards.straightdope.com/u/rexnervous)\
**Post date:** [August 7, 2003, 2:24am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/4 "2003-08-07T02:24:10Z")

</div>

thanks, but that didn’t do it. I’ve tried all kinds of variations of that, but it’s not doing anything.

No matter what I do, all I get is a seemingly unending string of “#” (###################…)

---

<div class="post-metadata">

**Author:** ![greyseal](https://avatars.discourse-cdn.com/v4/letter/g/bbe5ce/32.png) [@greyseal](https://boards.straightdope.com/u/greyseal)\
**Post date:** [August 7, 2003, 2:43am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/5 "2003-08-07T02:43:41Z")

</div>

> [@](#):
>
> No matter what I do, all I get is a seemingly unending string of “#” (###################…)

Make sure that the column is large enough to fit the formatted data in.

---

<div class="post-metadata">

**Author:** ![Dewey\_Finn](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dewey_finn/32/4222_2.png) [@Dewey\_Finn](https://boards.straightdope.com/u/Dewey_Finn)\
**Post date:** [August 7, 2003, 2:43am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/6 "2003-08-07T02:43:42Z")

</div>

Assuming that the data is in cell A1, try this in cell A2:

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))

---

<div class="post-metadata">

**Author:** ![greyseal](https://avatars.discourse-cdn.com/v4/letter/g/bbe5ce/32.png) [@greyseal](https://boards.straightdope.com/u/greyseal)\
**Post date:** [August 7, 2003, 2:58am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/7 "2003-08-07T02:58:22Z")

</div>

Actually, after fooling around with this example a bit, it doesn’t seem to work.

**SMTWTFS** seems to have the best workaround.

---

<div class="post-metadata">

**Author:** ![5\_time\_champ](https://avatars.discourse-cdn.com/v4/letter/5/278dde/32.png) [@5\_time\_champ](https://boards.straightdope.com/u/5_time_champ)\
**Post date:** [August 7, 2003, 3:09am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/8 "2003-08-07T03:09:03Z")

</div>

FORMAT/  
CELLS/  
NUMBER :

####-##-##-######

returns:

2003-01-25-115000

Appears much more “readable”

---

<div class="post-metadata">

**Author:** ![rexnervous](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rexnervous](https://boards.straightdope.com/u/rexnervous)\
**Post date:** [August 7, 2003, 3:19am UTC](https://boards.straightdope.com/t/aargh-can-anyone-help-me-custom-format-this-numeral-in-excel/193493/9 "2003-08-07T03:19:54Z")

</div>

Thanks for replies. I did it **SMTWTFS** ’s way.