Dates on Horizontal Axis all revert to 1st of the Month

[markhib]

Hi There,

i have searched a few threads but nothing i can use, i am plotting month end dates across the horizontal axis, they are not the last day of the month but the last friday of the month, hence no standard gap from one month to the next. isuspect this is why excel is changing all dates to the 1st of the month, so 22nd Feb becomes 1st Feb, 25th Jan becomes 1st Jan etc etc. Any Suggestions?

Cheers

Hibs

 

[Yongle]

Converting dates to text works for me

Enclose your date inside =TEXT
=TEXT(CURRENT FORMULA OR CELL REF HERE ,"dd/mm/yy")

=TEXT(DATE(YEAR(A2),MONTH(A2)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,0),2)-2,-7),"dd/mm/yy")