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

 

[McTam]

Apologies that didn't format correctly!! Column H is TODAY, I is the contact client cell.

 

[oldbrewer]

highlight this data range, color it yellow and put borders around any cell then copy and paste it into the reply box here

 

[McTam]

It wont let me do that I'm afraid. I used to upload using an html tool but dont have it on this laptop.
A B C D E F G H
Type Address Account Contact Phone E-Mail Date Due Contact Client
The due date is date we need to do Service so Column H (Contact Client) needs to show a date two weeks before due date so we can contact the Client. Sorry to be such a pain over this would just like to get it in place as I am doing it with Sage Diary at the moment and it is limited as to what it can do.
Thanks again Alex

 

[oldbrewer]

[TABLE="width: 619"]
<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]col 1[/TD]
[TD]col 2[/TD]
[TD]Col #3[/TD]
[TD]Col#4[/TD]
[TD]Col#6[/TD]
[TD]Col#7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]service from[/TD]
[TD]service to[/TD]
[TD]invoice amount[/TD]
[TD]due date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD="align: right"]300[/TD]
[TD]2/15/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/06/2018[/TD]
[TD="align: right"]03/06/2018[/TD]
[TD="align: right"]500.25[/TD]
[TD]3/21/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Col #3[/TD]
[TD]Col#4[/TD]
[TD]Col#6[/TD]
[TD]Col#7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]service from[/TD]
[TD]service to[/TD]
[TD]invoice amount[/TD]
[TD]due date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD="align: right"]300[/TD]
[TD]2/15/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/06/2018[/TD]
[TD="align: right"]05/06/2018[/TD]
[TD="align: right"]400[/TD]
[TD]6/20/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]assuming today is March 28 you want to retrieve 500.25 from the top table[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and nothing from the lower table ?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[McTam]

Sorry I am probably not explaining myself properly. All I need is to flag up when a service is due so I can send out a reminder in an e-mail or phone call. We undertake gas servicing for clients and landlords which are annual so I just need to be reminded two weeks before the due date so I can contact the people. So Column H is two weeks before the Due Date and I need to highlight the cell to remind to contact the client. Hope that makes a little more sense? I Essentially I only need a formula in the cell in column H to go red to show it's due. Just can't remember how to do it. Sorry,

thanks again Alex

 

[McTam]

I am using this formula in column I but I need it to look at < TODAY as well,
=IF(H2=TODAY();"YES";"")
Is there an OR formula to use two variables?
thank you

 

[McTam]

Sorry it would also need to clear if it is over two weeks as well otherwise it will always sat TODAY. Getting a bit confused myself to be honest. So what I would need if a formula that shows the due date as YES (which I have formulated in Red) using TODAY. It would need to stay as TODAY for 'maybe' ten days? then clear the cell. I hope that all makes sense??
Thank you all in advance Alexx