Date Question

[millwalll]

Hi all,
I have a date for example 23/10/07 then I have a contract length let say 18.
I need to add that to the date and if the new date is less than the
current date I need a cell to dispaly Out contract

Many thanks

 

[Mike H]

Hi,

Contract start date In A2
Length of contract in months in B2
This in C2
=IF(DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))<TODAY(),"Out of Contract","")

Mike

 

[millwalll]

Hi thanks dont supose you could explain it to me of what each bit does thanks

 

[Mike H]

Hi,

=IF(DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))<TODAY()
,"Out of Contract"
,"")

The formula has 3 parts which are split above onto 3 lines.
Part 1. Adds the value in B2 in months to the date in A2 and checks if is
earlier (<) than today. You can do a simple mathmatical < comparison with
dates because they are actually stored as numbers. Change the format of a
cell with a date in to general and see what happens.

Part 2 is what to display if part 1 is TRUE
Part 3 is what to do if part 1 is false which in this case is "" or nothing.

Mike

 

[millwalll]

Thanks

Hi,

=IF(DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))<TODAY()
,"Out of Contract"
,"")

The formula has 3 parts which are split above onto 3 lines.
Part 1. Adds the value in B2 in months to the date in A2 and checks if is
earlier (<) than today. You can do a simple mathmatical < comparison with
dates because they are actually stored as numbers. Change the format of a
cell with a date in to general and see what happens.

Part 2 is what to display if part 1 is TRUE
Part 3 is what to do if part 1 is false which in this case is "" or nothing.

Mike