Excel help

Gers

Morning.

I'd appreciate some technical help with an Excel spreadsheet please.

Col A has a date in which is ahead - i.e. 16/11/16

I would like a formula which highlights, in Col B, when the date has two months to go before the Col A date is reached.

I need to warn students of the impending end of their registration (and encouorage them to do some blooming work!)

Can it be done? I've tried conditional formatting but can't work it out.

Thanks

rmg1

If you want to base it on todays date, you can use something like :-
=if(a2-today()<=60,"Y","")

This will put a Y in the relevant cell if there's 60 days or less than the date you've entered to go.

You can then conditionally format that.

[Deleted User]

Do you mean you just want to subtract 2 months from the value in a cell?

You'd use a function like this:

=EDATE(A1,-2)

Then make sure you set the format of the output column as a date format, or it'll just show a weird numeric version of the date

You can turn that into a True/false if you add a condition:

=IF( TODAY() < EDATE(K7,-2), "TRUE", "FALSE")

Then throw on a bit of conditional formatting to highlight stuff

Gers

rmg1 wrote: »

If you want to base it on todays date, you can use something like :-
=if(a2-today()<=60,"Y","")

This will put a Y in the relevant cell if there's 60 days or less than the date you've entered to go.

You can then conditionally format that.

Thanks, though I won't be using todays date but different dates, mostly from 2015.

ringo_24601 wrote: »

Do you mean you just want to subtract 2 months from the value in a cell?

You'd use a function like this:

=EDATE(A1,-2)

Then make sure you set the format of the output column as a date format, or it'll just show a weird numeric version of the date

You can turn that into a True/false if you add a condition:

=IF( TODAY() < EDATE(K7,-2), "TRUE", "FALSE")

Then throw on a bit of conditional formatting to highlight stuff

Thanks very much both of you, your help is appreciated.

Off to have a go!

Gers

ringo_24601 wrote: »

Do you mean you just want to subtract 2 months from the value in a cell?

You'd use a function like this:

=EDATE(A1,-2)

Then make sure you set the format of the output column as a date format, or it'll just show a weird numeric version of the date

You can turn that into a True/false if you add a condition:

=IF( TODAY() < EDATE(K7,-2), "TRUE", "FALSE")

Then throw on a bit of conditional formatting to highlight stuff

Brilliant - that worked perfectly (the first option).

Many thanks.