We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Excel driving me mad


Housten

Likes # 0

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!!!

Like this post
Forum Editor

Likes # 0

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)

Like this post
Zak

Likes # 0

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

Like this post
Zak

Likes # 0

Add

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

=VALUE(TEXT(C4,"ddmmyyyy"))

in order to show date format in our usual way.

Like this post
Housten

Likes # 0

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!

Zak,

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!!

Like this post
Zak

Likes # 0

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"))

Like this post
Forum Editor

Likes # 0

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.

Like this post
Housten

Likes # 0

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!

Like this post
Housten

Likes # 0

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”!!!

Zak,

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.

Like this post
Simsy

Likes # 0

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;

yyyymmdd

That works for me.

Apologies if I've missed something!

Regards,

Simsy

Like this post
Housten

Likes # 0

Simsy,

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.

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

3 of the best portable chargers: a solar power charger, a hand-cranked charger, and how to charge...

IDG UK Sites

iOS 8 review: Hands on with the iOS 8 beta

IDG UK Sites

Thinking robots: The philosophy of artificial intelligence and evolving technology

IDG UK Sites

Sharknado 2 VFX: how The Asylum created CG flying man-eating sharks