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.
Labels:

Comments

Post a Comment

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...
14 comments
Read more

Javascript form validation

One of the most popular uses of Javascript is when it comes to validating user input on a form. The most hateful way, though, is by not using onsubmit. A number of times I come across code where someone uses an image in place of the submit button and uses the onclick event which will validate then submit the form. Yeauch! Very bad practice! The first thing to do with the form is place the Javascript function in the onsubmit attribute for the form, e.g. <form name="reg" method="post" onsubmit="return validateForm();" The use of return lets the form submit when the function returns true. If the function does not return true (i.e. false) then the form will not submit - as shown below. function validateForm(){ return false; } Getting a reference to the form is the next step. There are a number of ways to do this: document.forms[0] document.forms['reg'] document.getElementById('reg') This will work when id is used on the form Passing an o...
Post a Comment
Read more

Microsoft Bing

It's nice to see the Press taking a much more keen interest in technology and gadgets. So I wasn't that much surprised when the Metro covered the launch of Microsoft Bling. Currently in beta Microsoft is obviously renewing their competition against Google. I've also noticed that Microsoft has recently replaced the Live search with Bing on their MSN page. But after using it, why do I get the impression that it's just copying Google with a more colourful interface?
Post a Comment
Read more