Issue with change date format

[iadrika21]

Hi All,

I'm using the below formula to change the format of the the date in a certain cell to the correct one (setting the format of the column to dd-mmm-yy):
=DATE(YEAR(RC[-1]),DAY(RC[-1]),MONTH(RC[-1]))
[=DATE(YEAR(B88),DAY(B88),MONTH(B88)) in a real example]

However after a certain line (when the day number is greater than 12) the formula gives incorrect as a result.
I'm really puzzled and have no idea how to fix this. Please see a screen shot below:
sample_date.JPG

Have you experienced this before? How can I fix it?! It's driving me nuts!!

Thanks a lot for your help and feedback in advance!

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[BadlySpelledBuoy]

Just out of interest, if all you're doing is changing the format of a date why are you using an additional column and this formula at all?

Try swapping the day and month element of the formula around so it's Year/Month/Day.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

BSB

[iadrika21]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hi AliGW,
Thanks a lot for the feedback! Attached a sample.

C2 has the end result I would like to achieve.
Column B is the data I pull from our system and its pulled in a "General" format. If I change the format to "Date", the cell data doesn't change automatically. If I click F2 + Enter, then it changes to "Date", but incorrect one (i.e. not to 2-Aug-17 as it should be, but 8-Feb-17). This incorrect data is represented in Column D (D2 - D25 I did F2+Enter so you can see the incorrect change, but left the rest untouched so you see that it doesn't change automatically)
Column E has the data where I used the above formula. This does the job, but only where the day is equal or smaller then 12.

Hope it is clear, please let me know if you would need further clarification.

Thanks a lot for the help!

[iadrika21]

Just out of interest, if all you're doing is changing the format of a date why are you using an additional column and this formula at all?

Try swapping the day and month element of the formula around so it's Year/Month/Day.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

BSB

Nope, not working.
Please see my comment to AliGW and also check the attached file.

[shivya]

There are many techniques to change the date format.
Please attach your excel sheet with it.

you can try:-
1. Enter the formula =DATEVALUE(RIGHT(A1,2)&"-"&MID(A1,6,3)&"-"&LEFT(A1,4)) into the blank cell B1.
2. Then tap the Enter key, select cell B1, and then drag the fill handle to the range you need.

or

=datevalue(A2)

Use them, which is suitable otherwise please attach your excel sheet.

[BadlySpelledBuoy]

Please Login or Register  to view this content.

BSB

[Root_]

... However after a certain line (when the day number is greater than 12) the formula gives incorrect as a result...

Try this:

=--(RIGHT(B88,4)&"-"&MID(B88,4,2)&"-"&LEFT(B88,2))

[iadrika21]

There are many techniques to change the date format.
Please attach your excel sheet with it.

you can try:-
1. Enter the formula =DATEVALUE(RIGHT(A1,2)&"-"&MID(A1,6,3)&"-"&LEFT(A1,4)) into the blank cell B1.
2. Then tap the Enter key, select cell B1, and then drag the fill handle to the range you need.

or

=datevalue(A2)

Use them, which is suitable otherwise please attach your excel sheet.

Thank you!
I ended up using =DATE(RIGHT(B6,4),MID(B6,4,2),LEFT(B6,2)) at the end which worked.

[iadrika21]

Try this:

=--(RIGHT(B88,4)&"-"&MID(B88,4,2)&"-"&LEFT(B88,2))

Thank you!
I ended up using =DATE(RIGHT(B6,4),MID(B6,4,2),LEFT(B6,2)) at the end which worked.