Excel Spreadsheet to show Due dates

[McTam]

Good morning all, been a while since I have been on here so hope everyone is well. I am a bit rusty and trying to put together a spreadsheet to show reminders for Gas Servicing. Most of it is pretty straightforward in that it would be

Type Address Account Contact Phone E-Mail Date Due

I would need the sheet to flag the due date two weeks before the service is due. Just not sure of best way to do it. Ideally I would like to be able to use the sheet to send an e-mail (or link to one) but not sure how practical this would be?
thank you in advance as always,

regards Alexx

 

[oldbrewer]

you can turn the whole row yellow if within 4 weeks of due date, orange within 2 weeks and red within 2 days using conditional formatting

easy to extract only the ones close to due date and put in another sheet that could be copied and pasted into an email - is this of any use to you....

 

[marshy3300]

Sorry Delete as something weird going on with the formula being displayed.

=if(g2 lessthansymbol "today"openclosebracket,"service due" otherwise "")

 

[McTam]

That sounds like what I need just not sure how to do it? I understand Conditional Formatting but not sure how to do it with dates.

thank you Alex

 

[oldbrewer]

	01/02/2018						23/03/2018		today's date
	07/02/2018
	13/02/2018
	19/02/2018
	25/02/2018	green
	03/03/2018	green
	09/03/2018	orange					requirement
	15/03/2018	orange
	21/03/2018	red					within 4 weeks = yellow
	27/03/2018						within 2 weeks = orange
	02/04/2018						within 3 days = rfed
	08/04/2018
	14/04/2018
	20/04/2018						as we cannot show color on here
	26/04/2018						I have used the next column
	02/05/2018						to indicate the color
	08/05/2018
	14/05/2018
	20/05/2018
	26/05/2018						in B1 the 3 equations are, in order
	01/06/2018
	07/06/2018						=AND($H$1-B1 < 4,$H$1-B1 > =0)
	13/06/2018						=AND($H$1-B1 < 4,$H$1-B1> =0)
	19/06/2018						=AND($H$1-B1 < 29,$H$1-B1 > =0)
	25/06/2018
	01/07/2018

<colgroup><col><col><col span="5"><col><col span="4"></colgroup><tbody>
</tbody>

 

[oldbrewer]

01/02/2018

23/03/2018

today's date

07/02/2018

13/02/2018

19/02/2018

25/02/2018

green

03/03/2018

green

09/03/2018

orange

requirement

15/03/2018

orange

21/03/2018

red

within 4 weeks = yellow

27/03/2018

within 2 weeks = orange

02/04/2018

within 3 days = rfed

08/04/2018

14/04/2018

20/04/2018

as we cannot show color on here

26/04/2018

I have used the next column

02/05/2018

to indicate the color

08/05/2018

14/05/2018

20/05/2018

26/05/2018

in B1 the 3 equations are, in order

01/06/2018

07/06/2018

=AND($H$1-B1 < 4,$H$1-B1 > =0)

13/06/2018

=AND($H$1-B1 < 15,$H$1-B1> =0)

error corrected

19/06/2018

=AND($H$1-B1 < 29,$H$1-B1 > =0)

25/06/2018

01/07/2018

<colgroup><col><col><col span="5"><col><col span="7"></colgroup><tbody>
</tbody>

 

[McTam]

Sorry for the delay in responding. I have tried this formula with the fields I mentioned above,
Type Address Account Contact Phone E-Mail Date Due
But I am getting an error message. Do I need to alter the cell references?
thank you Alex

 

[oldbrewer]

B1 is a date and H1 = today()

alter those if your data is elsewhere

 

[McTam]

I have altered my sheet to fit your formula but I am still getting an error message I'm afraid,

thank you Alex

 

[McTam]

This is the sheet as it if formatted. Just need Column I to show service is due.

A B C D E F G H I
Type Address Account Contact Phone E-Mail Date Due =Today Contact Client
27/02/18
23/03/18
06/03/18