98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Converting dates/times
Converting dates/times
Resolved · Medium Priority · Version 2010
Thu 14 Mar 2013, 10:48Delegate Laura said...
Laura has attended:
Excel Advanced course
Converting dates/times
Hi there,
Need to convert thousands of dates and times on excel. They are provided in the layout of YYYYMMDD (without separations), but need it in the format DD/MM/YYYY. The usual format cells;dates, will not help in this instance. I am currently having to use text to columns to split the long figure into three columns (eg 20111201 moves to 2011 12 01), format cells to text to add the 0 before months/days 1-9, and then use a formula to join all 3 parts back together again in the correct order. Is this the only way to achieve this!?
On a similar, but much harder note, time's are provided in the form of HHMMSS (without separations), and i need it to be HH:MM. With the times around midnight, which obviously begin with 0, they can appear as just three figures (eg, 156 actually equals 00:01:56). I am currently manually typing those around midnight, and have managed to split the time as far as using =left(time cell;2) to split the first 2 numbers of the time, (on the assumption once the time is split I can stick it together again the same way as the date) but am otherwise stuck here. I need an easier way to convert the time, surely that too much also exist!?
Thanks for your help! It'll be greatly appreciated and used for many years should a solution exist!
Thanks
Thu 14 Mar 2013, 12:04Trainer Gary said...
RE: Converting dates/times
Hi Laura
Thanks for getting in touch. Dates and times are always a challenge in Excel. Let's deal with this in two parts.
First the dates: you are on the right lines but you can split it all up and put it back together again in one formula. Assuming your date is in A1, it will be something like this:
=CONCATENATE(RIGHT(A1,2),"/",MID(A1,5,2),"/",LEFT(A1,4))
The LEFT, MID and RIGHT functions extract parts of a cell. The CONCATENATE function is gluing it all together. You should end up with a real date you can manipulate.
The time one is a bit more tricky, could you reply with a list of sample times that are outputted, and what you would like them to look like? It will be similar to the date answer but possibly some IF logic in there to append a leading zero if less than 10.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 14 Mar 2013, 12:23Delegate Laura said...
RE: Converting dates/times
Hi Gary,
Fantastic thanks I'll give that a go for the date. I've pasted below a sample of times I would receive:
203551
145500
82500
1436
These mean 20:35:51secs, 14:55:00secs, 08:25:00secs, 00:14:36secs. It needs to be turned into 20:35, 14:55, 08:25, 00:14.
I know this will be a tricky one; intrigued to know if there is a way!
Thanks for your quick help.
Thu 14 Mar 2013, 15:41Trainer Gary said...
RE: Converting dates/times
Hi Laura
Thanks the example. Just trying to capture all the cases: what is output for single minutes, e.g. 10hrs, 2mins, 5secs ?
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 15 Mar 2013, 09:45Delegate Laura said...
RE: Converting dates/times
Single minutes in the example you've provided would look like 100205.
It will always be 6 digits long, it's just where the first few numbers are zero's excel will automatically delete them. So for instance where 1436 is provided above (to mean 00:14), it should look like 001436.
Thanks.
Fri 15 Mar 2013, 10:31
Edited on Fri 15 Mar 2013, 10:31Trainer Gary said...
RE: Converting dates/times
Hi Laura
Try this:
=CONCATENATE(IF(LEN(A2)=6,LEFT(LEFT(A2,LEN(A2)-2),2),IF(LEN(A2)=5,CONCATENATE("0",LEFT(LEFT(A2,LEN(A2)),1)),"00")),":",RIGHT(LEFT(A2,LEN(A2)-2),2))
I've attached a workbook with some examples.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Dates and Times.xlsxFri 15 Mar 2013, 15:01Delegate Laura said...
RE: Converting dates/times
Hi Gary,
Apologies for the late reply I've been in meetings inbetween playing around with this! Slightly unusually it wont work when i paste the formula outright onto my worksheet (I either receive #NAME in the cell or error, depending which cell i paste into), yet works exactly as needed when i copy the dates/times onto the worksheet you sent and continue the formula down!
So, basically, it still works a treat. Copying over to the worksheet provided formula (which will now become the master copy!) and pasting values back to my original is still quicker than before. Thanks for your help with this.
Laura
Fri 15 Mar 2013, 15:10Trainer Gary said...
RE: Converting dates/times
Hi Laura
Good to know it works. Not sure why you're seeing the NAME error.
Your next step will be to turn those two stages (1. create formula 2. paste values) into a macro. That would make everything fly at the click of a button :-)
Enjoy playing around with that.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 22 Mar 2013: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quickly hide and unhide rows and columnsUse the keyboard shortcut Ctrl+9 to hide selected rows and Ctrl+0 to hide selected columns. The good thing about this shortcut is that you do not need to select entire rows or columns. For example, select B3:D3 then press Ctrl+0 to hide columns B to D. |
