Convert number string to a date

dsouza

New Member
Joined
Jan 18, 2017
Messages
3
Hi,

Can you please give me the calculation I need in order to convert the following into a date that excel can read?

31072016
01082016
01082016
01082016
01082016
01082016
ETC

Many thanks,

Viv

<tbody>
</tbody><colgroup><col></colgroup>
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,256
Office Version
  1. 365
Platform
  1. Windows
Assuming you always have the leading zeroes for single figure days/months then this works

=DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))
 

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
In this case, you shouldn't need a formula, just select the cells, and use Text to Columns (In the data tab) and choose date in the column data format.
 

dsouza

New Member
Joined
Jan 18, 2017
Messages
3

ADVERTISEMENT

Thanks guys, most helpful
 

Amanda Wilso

Board Regular
Joined
Dec 18, 2016
Messages
132

ADVERTISEMENT

=TEXT(A1,"0000-00-00")+0
or
=--TEXT(A1,"0000-00-00")

Format the cell containing this formula with an appropriate date format.

 

dsouza

New Member
Joined
Jan 18, 2017
Messages
3
Sorry to ask a stupid question folks. How do I close this thread now you have so gratiously given me a solution?
 

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Sorry to ask a stupid question folks. How do I close this thread now you have so gratiously given me a solution?

On this forum there is no way for you to mark a thread as closed or solved. The best thing to do is exactly what you did in post #5.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top