Calculating a date

MarkoBrit

New Member
Joined
Mar 6, 2019
Messages
11
Hi I have a spread sheet which shows employees (trainees etc) who are under 18.
In cell B2 I have thier DOB. Then in C2 I want a formula that returns the date of when they would reach 18. Anyone know how to do that?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,558
Office Version
2019
Platform
Windows
Data Range
A
B
C
1
EE​
DOB​
18​
2
A​
37411​
=B2+(18*365.25)​
3
b​
37803​
=B3+(18*365.25)​
4
c​
38204​
=B4+(18*365.25)​
5
d​
37141​
=B5+(18*365.25)​

<tbody>
</tbody>


Because I am showing this as formulas, the DOB shows numerical and not date format. Should not make a difference when you do your actual file.

Data Range
A
B
C
1
EE​
DOB​
18​
2
A​
6/4/2002​
6/3/2020​
3
b​
7/1/2003​
6/30/2021​
4
c​
8/5/2004​
8/5/2022​
5
d​
9/7/2001​
9/7/2019​

<tbody>
</tbody>


You may want to change the multiplier to 365 instead of 365.25
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,587
Office Version
365
Platform
Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Tom Pearce</td><td style="text-align: right;;">29/04/2019</td><td style="text-align: right;;">29/04/2037</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Bill Brewer</td><td style="text-align: right;;">15/03/2011</td><td style="text-align: right;;">15/03/2029</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Jan Stewer</td><td style="text-align: right;;">25/08/2012</td><td style="text-align: right;;">25/08/2030</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Peter Gurney</td><td style="text-align: right;;">15/06/2013</td><td style="text-align: right;;">15/06/2031</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Peter Davy</td><td style="text-align: right;;">15/06/2010</td><td style="text-align: right;;">15/06/2028</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Dan'l Whiddon</td><td style="text-align: right;;">22/05/2000</td><td style="text-align: right;;">22/05/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Harry Hawke</td><td style="text-align: right;;">11/03/2005</td><td style="text-align: right;;">11/03/2023</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Engine</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=EDATE(<font color="Blue">B2,216</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,098,981
Messages
5,465,794
Members
406,450
Latest member
malar11567

This Week's Hot Topics

Top