Custom Date format and calculations

Greep3r

New Member
Joined
Aug 28, 2019
Messages
3
Good Day

I am trying to find a way to type and only indicate the number of years and months in one cell. This format must also then be used to calculate and end date for a contract term.

I would like to be able to display the cell value as 9 y 11 m or 2 y 3 m etc...

This must then be added to a start date of for example 01 Oct 2019 to display an end date.

I have tried custom formatting like y "y" m "m" but this requires a full date to be typed and will not work for this purpose.

Any suggestions will be helpful.

Thanks
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
With a start date in A1

Format the year and month cell (B1) as Custom format

00\y 00\m

Enter the years and months as TWO digits each, e.g. for 1 year and 2 months enter 0102

in C1
=EDATE(A1,INT(B1/100)*12+RIGHT(B1,2))
format as a date

Adjust the cell references in the formulas to reflect the cell references in your spreadsheet
 
Last edited:

Forum statistics

Threads
1,082,576
Messages
5,366,419
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top