Skip to main content

Working with the Date in Excel

I was just asked by my colleague how to turn 20080101 (in a file provided by a client) to 01/01/2008 (to be used in our system) in Excel where the cell is presented as text. We can easily recognise the date, but computers are not so intelligent to put up with these variations of how we operate.

There are a number of ways, but mine relies on certain assumptions, that numbers below 10 are pre-padded with a zero (0), ie. the 9th is represented as 09. This method uses the VB functions in Excel, but for simpilicity's sake I will break this down into stages.

For this example, I will use the above date (20080101) which is in cell A1.

Firstly, we need to split the date in and use cells A4, B4, and C4:



So we need to use a formula in A4 to get the year, B4 to get the month and C4 to get the day:
  • Year =LEFT(A1,4)
  • Month =MID(A1,5,2)
  • Day = RIGHT(A1,2)
The LEFT function is to get the first four digits. The MID function is to get the middle 2 digits starting from the 5th position. The RIGHT function is to get the last 2 digits.

We have now effectively broken down the date into usable chunks, so you can do anything with the formulas. However in my situation I need to present the date differently by using the CONCATENATE function.



This is achieved with the following formula in cell A8: =CONCATENATE(C4, "/", B4, "/", A4)

And there you have it.

Comments

Popular posts from this blog

Open Source alternatives to Adobe Creative Suite

With the take over of Macromedia by Adobe, they have increased their arsenal of design packages, as well as the price. No-one can do pretty much without the popular package of Photoshop for their images, and Dreamweaver for websites, especially aspiring design students who do not have much money.

Nevertheless, open source software has come to save the day. What is open source software? In a nutshell it is free software with its source-code freely available to those who would like to expand or improve on the software. To expand further, anyone who’s tinkered with the source code such as optimisation or additional functionality can resubmit it back to the holders and let others enjoy the fruits of your labour.

I have trawled through the World Wide Web to find out how everyone can benefit from Open Source to build up their own studio and compete against Adobe Creative Suite. All this for free, apart from the cost of the Internet. I am planning to have a facility where you can purchase th…

Open Source alternatives to Adobe Creative Suite update

Oh yes, an update to an earlier post about getting hold of free or Open Source alternatives to Adobe Creative Suite.

I actually forgot to mention about Adobe ImageReady, which gets bundled with Photoshop. There are a number of basic animation tools, but none provide flexibility similar to ImageReady. Fortunately, Open Source lovers are in for a surprise in the form of GIMP Animation Package. This link will take you to the Windows download site as I cannot find any references to this for the Mac OS. Chances are that it could be part of the Gimp.app download, but I haven't had time to inspect this.

The Creative Suite package includes Adobe Bridge, which is a thumbnail viewer to convert images and open them up in any Adobe application for editing. No matter how they would try to trump up its name, it is still a thumbnail viewer. The best free application out there for this is IrfanView. Need I say more?

Are there any pixel pushers out there, who prefer to simply draw their own pics? Why…

ASP Reverse For Loop

It's kind of interesting of how reverse linking does not seem so obvious in classic ASP.  You would think that it would be along the lines of:

dim i
for i = 30 to 2
  'code comes here
next

but it's not.  To have a reverse For loop it's:

dim i
for i = 30 to 2 Step-1
  'code comes here
next

That's right that small bit (Step-1) makes all the difference.  I stumbled upon this by chance and thought that someone would benefit from this.

Happy coding.Blogged with Flock