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

Will we ever get Dreamweaver on Linux?

I have been a keen user of Ubuntu for some time now, and have to admit that this distribution has improved by leaps and bounds. However, what I do honestly miss on windows is... Dreamweaver. Some will swear by it, some will swear at the mention of its name. But for me, it is a very powerful tool when used correctly. Such a very popular and powerful web design and development tool is available on Windows and Mac OS, but not Linux. If you would just consider, Adobe has already supported the platform with their Adobe Reader and Adobe Air. Why is Dreamweaver not available? It just seems that there's not such a huge call for it so they just don't see the need to put in an effort for another platform. One chap at Adobe suggested to use Dreameaver under wine , but it doesn't work so great. There is an alternative to wine which, according to a user on the forums, works flawlessly - Crossover . However, the former option is not free, so some may be dissuaded where they woul...
Post a Comment
Read more

Image links in IE8

While mocking up a design page for a colleague's website I came across a new browser behaviour in IE8. This was the first time I used IE8 as my main development browser, due to the fact that the PC was re-installed and high-priority updates were applied. Surprisingly Microsoft branded IE8 as a priority update. Anyway, back to what I was talking about, here's a sample: <a href="index.htm"><img src="logo2.jpg" alt="Logo" title="Logo" /></a> As you can tell, this is using an image for a link, where it behaves well in Chrome, Firefox and in the previous versions of IE (7 and older). But in IE8, this is not the case as the DOM does not treat the image as innerText, only as a child node. As a result, everytime I hover the mouse over this image, an extra gap would come from nowhere shifting any relatively positioned elements. To fix this I explicitly added a space (&nbsp;), so the line would look like: <a href="inde...
3 comments
Read more

Morange

I have Nokia N70 on the T-Mobile network, and it's really impressive. Labelled as a Smart Phone, it is a bit like a cross between a PDA and a normal mobile phone. Boy does it live to expectations. You can check your emails (POP/IMAP), read Word documents, Spreadsheets, view To-Do lists and so much more. Now I sound like a sales man. The most handy aspect is the ability of downloading "mini-applications". What I really want to talk about is a mini-application that let's you treat your phone as if it is an iPhone. Yes, you heard me. An iPhone. Why bother with a mobile phone that costs you an arm and a leg for a phone with a pretty interface. Well, I would, but I would not like to risk my limbs in the process. This application is called Morange ( http://www.morange.com ) and is feature rich. You can receive an "push" POP/IMAP emails. It even works with GMail. All you have to do is enable POP3 in the settings (Follow this link to find out how http://ma...
2 comments
Read more