Excel driving me mad

  Housten 13:08 25 Nov 2012

Afternnon, gentlemen,

This is a simple problem, or so I thought, for me!! What I wish to do is to take a date - this is in the form 25/11/2012 - and turn it into a numeric value as 20121125. I thought this was going to be so easy that I have left it as virtually the last thing to be done on my spreadsheet!! What an idiot I am proving to be, I have only been trying for the last three hours, AND with the help of a manual ( useless ). I thought that '=val(mid$(b5,7,4)+mid$(b5,4,2)+mid$(b5,1,2))' would do it. Not a bit of it, and I am going completely round the bend!! Could some kind person please put me out of my misery.

I am using Windows 7 64Bit Home Premium with Excel from Office Pro 2003.

I would greatly appreciate any help/advice/information that anyone can give me. I would prefer to know it can be done, rather than it is impossible as I am certain that the formula I quote above is whet I hve used before. But this is an obviously wrong statement, as it's not working!!! So please help a brain dead idiot!!!

  Forum Editor 13:18 25 Nov 2012

Assuming your date to be in A1 you could try this:-

=TEXT(DAY(A1),"00") & TEXT(MONTH(A1),"00") & RIGHT(TEXT(YEAR(A1),"00"),2)

  Zak 13:36 25 Nov 2012

Here is another way:

Converting Date to Number

This will convert to proper numbers which you can add etc. However if you want the leading zero in say 01122012, then you need to custom format by putting in 00000000 ( 8 zeros ) in the custom format field

  Zak 13:42 25 Nov 2012


Also you will need to change the formula shown in the link to


in order to show date format in our usual way.

  Housten 14:55 25 Nov 2012

Good Afternoon,

Forum Editor,

Not only did I try it - AND it worked!!, but I do not understand it or how it does it!! - at the moment. Do I care?? Not in the slightest!! It works, that is all I want at this moment. The date I wanted to be displayed was using "25/11/2012" as the base to produce 20121124 - I actually need the preceeding day, but until I could get the date there wasn't any point worrying about that point!! What I have actually done is to change the sequence and deduct one. Works perfectly!! Now it seems so easy, but I haven't got a clue how it does it, but I do not care a jot. IT works, it works, that is ALL I care about, many, many thanks!


Your version worked just as well as The Forum Editor's, apart from one tiny flaw. As I say in my comment and thank you to the Forum Editor, I need to use the preceeding day. No matter which way I have treid to make yours work it will not take the previous day, whereas the Forum Editor's makes no difference, so I will continue using his version, though many thanks for taking the time and trouble to do your post!

Many thanks to you both!!

  Zak 17:28 25 Nov 2012

If you should need my version here is a way of doing it:

  A                    B                  C

A1 Day Preceeding Day Numeric Value

A2 01/12/2012 =A2-1 =VALUE(TEXT(B2,"ddmmyyyy"))

  Forum Editor 18:49 25 Nov 2012

I'm glad you're happy - I know only too well how frustrating it is when one tiny detail suddenly stops you in your tracks.

  Housten 12:04 26 Nov 2012

Gentlemen, and Forum Editor,

Many, many thanks for all your help.

At the moment I am having another problem, but I may just abandon it, I have to try and see whether it is worth the time and effort!!! That is not only me, but you kind gentlemen as well!!!!

Many thanks once more, all your efforts are greatly appreciated!

  Housten 16:14 01 Dec 2012

Forum Editor,

You very kindly gave me a reply to my problem, but the solution you gave me was to an Excel problem I was having. This was to do with converting a text date to a numeric date. I do not understand, fully, how it works; all I was interested in was that it worked!! However I recognised that there were three parts to it and as I wished them in a different sequence. So I – after a couple of trials and errors – got your formula working. Well to start with!!! What I changed your formula to was “=RIGHT(TEXT(YEAR(A471),"00"),4)&TEXT(MONTH(A471),"00"&TEXT(DAY(A471),"00"))-1”. The end “-1” was to make it the previous day, and, as I said, it worked so that when the date was listed as “29/11/2012” your formula gave “20121128”. However what has now happened is that it has stopped working and I do not know why. And what has occurred is that for “30/11/2012” it gives “2012130” and for “01/12/2012” it gives “2012120”!! So what I then did was to have a calculation that gave the earlier day, but for “01/12/2012”, I had the earlier date as “30/11/2012” and my brevision to your formula gives me “20120131”!!!


What I have now done is to use your second suggestion, so “01/12/2012” gets converted to “30/11/2012”, and is enumerated as “20121130”, just what I want!!!.

So thank you both very much indeed for your time and trouble you have taken in helping me. Can I just say I have another problem that I have been wrestling with for some time, and which I do not believe to be solvable, but I will be asking in another post.

  Simsy 07:54 02 Dec 2012

Unless I'm missing something here...

This is really much much easier than all the above... at least it is in Excel 2010, (and Libre Office), and I'm sure it must be the same in Excel 2007...

All you have to do is format the relevant cells with a custom format;


That works for me.

Apologies if I've missed something!



  Housten 12:49 02 Dec 2012


You say "Apologies if I've missed something!". Many thanks for your answer but what you missed was "I am using Windows 7 64Bit Home Premium with Excel from Office Pro 2003." which is the penultimate paragraph. I will not be changing this again as it does virtually everything that I want, and as a pensioner, I have to look after my pennies.

I only hope someone else will be able to make use of your reply.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?