Need help with formula!

[BETHHOPKINS]

Hi guys,

I am trying to fix a spreadhseet as the date has come out like '12102020' rather than '12/10/2020' and when i format the cells it doesnt work.

I know there is a 'left' formula but i can't remember how to use it!

If someone could tell me what to put that would be great. Thank you in advance.

 

[Logit]

https://www.excel-exercise.com/date-format-in-excel/

 

[BETHHOPKINS]

Thank you for your response! Though this is what i get when i do that! I have tried all these options!

 

[Logit]

It would be best to post your code / formula.

Better yet, post your workbook to a download site so it can be examined.

 

[BETHHOPKINS]

I'm not using a code or formula? I simply formatted the cells as a date as per your link above and this is what happens.

I just need to know how the 'left' 'mid' and 'right' formula works so i can inset a'/' in betweeen the day, month and year if you could help me with that!

Thanks!

 

[Logit]

If you are not using a formula ... and you are not using VBA macro code .... there is not need to use the LEFT, MID, RIGHT commands in a formula to achieve your goal.

Right clicking the cell (or the entire column is desired), then selecting FORMAT CELLS ... is all you need.

You don't need to the select the CUSTOM format setting for your goal ... simply select DATE and see which formatting shown is the desired format.
If that doesn't work, there is something else going on with your workbook.

 

[BETHHOPKINS]

Right clicking the cell (or the entire column is desired), then selecting FORMAT CELLS ... is all you need.

You don't need to the select the CUSTOM format setting for your goal ... simply select DATE and see which formatting shown is the desired format.
If that doesn't work, there is something else going on with your workbook.

This is what i originally tried but all i get #######.

 

[Logit]

Wait ... I think I understand differently now what you are wanting.

Select the cell or cells / cell range that you want the formatting to be applied.
Again, right click any of those selected cells and select FORMAT CELLS ... then CUSTOM.

In the field TYPE: ... enter the same thing you see in the pic above : 00\/00\/0000

I tried this here and if you enter 02062020 it should display as 02/06/2020 automatically.

 

[Logit]

What I have learned from this formatting is ... if the month is entered as a single digit ... 1 to 9 ... the final display will error.

It is best to accept the keying habit of placing a zero "0" in front of all single digit numbered months.

 

[Dossfm0q]

use XLBB to post instead of picture
Posting Data use XL2BB version1.2.8 (8th Aug 2020)