• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Excel Date Help

SimonMac

TykeLike
Joined
Aug 27, 2010
Messages
24,897
Likes received
5,244
Thanks received
1,850
Location
God's Own Republic Of Yorkshire
I have a series of rows, first column should be Month/Year, however it always translates to day/month/year in any calculations.

I would like the date to be calculates based on one month after the cell above, I could use
Code:
today()+31
but obviously that wont work on every month, is there a simple way around this?
 
Excel Date Help

Change the format of the column to custom and set it to mm/yyyy. That will sort out the date bit, then just add a month on.
 
Not entirely sure I follow, but if you only want months, why not start in the middle of the month and increment by 365/12?
 
Scrap that

this works
Code:
=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))
 
Excel Date Help

That works, unless the previous month has more days than the current one, in which case you really need the day to always be e.g. 1.
 
That works, unless the previous month has more days than the current one, in which case you really need the day to always be e.g. 1.

The date is always the first of the month, I tried the custom MMM/YY, and although this changed the visable contents of the cell to Jul/16, it still calculated it as 01/07/2016
 

Advertisers

Back
Top