minus 1 year from now function

[melewie]

Hi all<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a spreadsheet with dates people started the company I need to figure out if they have been here over a year. In have tried several different ways and have figured out a few long winded ways to give me today’s date (Now()) minus 1 year. i.e.<o></o>
<o></o>
<TABLE style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 157pt; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0cm 0cm 0cm 0cm" cellSpacing=0 cellPadding=0 width=209 border=1 u1:str><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-RIGHT: windowtext 0.5pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 0.5pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 36pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=48>=Now()<o></o>

</TD><TD style="BORDER-RIGHT: windowtext 0.5pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 0.5pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt" width=75>=YEAR(A1)<o></o>

</TD><TD style="BORDER-RIGHT: windowtext 0.5pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 0.5pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt" width=87>=YEAR(A1-1)<o></o>

</TD></TR></TBODY></TABLE>
<o></o>
Question is. There must be an easier way to calculate this!? <o></o>
<o></o>
Any idea VBA solutions would be fine.<o></o>
<o></o>
Thanks in advance.

(Sorry about the amature cell bit not sure how to add cells in here)

 

[melewie]

Sorry Should be!!

<TABLE style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 157pt; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0cm 0cm 0cm 0cm" cellSpacing=0 cellPadding=0 width=209 border=1><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-RIGHT: windowtext 0.5pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 0.5pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 36pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=48>=Now()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
</TD><TD style="BORDER-RIGHT: windowtext 0.5pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 0.5pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 56pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt" width=75>=YEAR(A1)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 0.5pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 0.5pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt" width=87>=(B1-1)<o></o>
</TD></TR></TBODY></TABLE>

 

[xanksx]

Suppose the date of joining is in cell A2, use

=IF(TODAY()-A2>365,"More than a year","Less than a year")

 

[melewie]

Suppose the date of joining is in cell A2, use

=IF(TODAY()-A2>365,"More than a year","Less than a year")

Had something similar at first but then got told it was no good the company needs to see when people are up for review which happens 12 months after you start I.e. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Bob started 01.03.2008 he is up for review 01.03.2009<o></o>
<o></o>
Thanks for the help though, any other ideas??

 

[Special-K99]

So what happens if someone started leap year 29/02/2008?
There is no 29/02/2009 what would you do there?

 

[mikerickson]

With join date in A1

=IF(AND(YEAR(NOW())=YEAR(A1)+1,TEXT(A1,"mmdd")=TEXT(NOW(),"mmdd")),"one year","not")

 

[mikerickson]

This might be more useful
=CHOOSE(2+SIGN(TODAY()-DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))),"less than yr","exactly 1 yr","more")

 

[melewie]

They both work brilliantly, now I don’t know which to use<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 12pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/biggrin.gif" src="file:///C:/DOCUME~1/u522393/LOCALS~1/Temp/msoclip1/02/clip_image001.gif"></v:imagedata></v:shape><o></o>
<o></o>
Thanks for the help<o></o>

 

[mikerickson]

The formula in post #7 allows you to distinguish between "review not due" and "review overdue".