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

markhib

New Member
Joined
Apr 21, 2008
Messages
17
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,786
Office Version
  1. 365
Platform
  1. Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,942
Members
409,848
Latest member
Blomsten
Top