How do I get a formula to amend certain cells only

[ghrek]

Hi

I have a formula in a excel sheet that changes the date as it will not change using any other methods.

The issue im having is that it is amending some dates that I do not want to amend. I have noticed that the cells that need amending the date is on the right hand side. Is there anyway I can run the formula I have but only amend the cells that are on the right hand side.

Formula is =IF(AND(DAY(C525)<=12,MONTH(C525)<=12),DATE(YEAR(C525),DAY(C525),MONTH(C525)),C525).

see screen shot on what mean regarding right hand side. Dont know it not showing on the picture but in my workbook line 523 the date is on the left next to the number and I DONT need that amending but the other lines are next to missing from station and there I DO need amending.

missing shifts .xlsm
	A	B	C	D	E	F
523	2491	216	25/07/2020	£0.00	MISSING FROM STN	25/07/2020
524	2492	163	07/07/2020	£24.45	MISSING FROM STN	07/07/2020
525	2492	163	07/07/2020	£64.00	MISSING FROM STN	07/07/2020
526	2492	163	07/08/2020	£2.20	MISSING FROM STN	08/07/2020
527	2492	163	07/08/2020	£5.70	MISSING FROM STN	08/07/2020
Sheet3

Cell Formulas
Range	Formula
F523:F527	F523	=IF(AND(DAY(C523)<=12,MONTH(C523)<=12),DATE(YEAR(C523),DAY(C523),MONTH(C523)),C523)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F523:F543	Expression	=F523<>C523	text	NO

 

[jasonb75]

It sounds like you are trying to use imported data with US dates on a computer with UK regional settings or vice versa.

If that is the case then all of the dates need amending, even those that don't look as if they do.

The best way to fix this is with text to columns on the original data (column C) which will amend the dates in place.

To do this select all of the dates in column C.
Click 'Text to columns' (on the data tab).
Click Next.
Uncheck All boxes and click Next.
Change the date dropdown to MDY. (DMY if your data is in UK format and you need to convert to US).
Make sure that the radio / option button is on Date and has not moved down to 'Do not import column (skip).
Click Finish.

 

[ghrek]

Ive tried that previously and it just not changing for some reason. Thats why I had to use the formula and that works with a few glitches.

 

[jasonb75]

If text to columns isn't working then it is likely that either you're trying to use the incorrect regional settings or there are invisible characters in the dates that are causing problems.

If you change the format of the dates in column C to number instead of date, which ones still look like dates?

 

[ghrek]

the ones that have dates on the left stay the same and the ones on the right stay as number

 

[jasonb75]

With the ones on the right being those where the day is greater than 12? 25/07/2020 for example?

If that is the case then you need to set the date dropdown as DMY when you follow the data validation steps.

*** You will need to change the format of column C to UK date (DD/MM/YYYY) before attempting text to columns. ***

 

[ghrek]

yes will give that a try. thanks