Excel formula help, any gurus out there?

Rod924

LE
Kit Reviewer
#1
Not sure this is posted in the right forum; mods please move if required.

I have put together a maintenance contract spreadsheet and I am struggling to make it work better. Details are

Column a - contract start date
B - contract end date
C - "+B-30" (Reminder send out date)
D - "date"
E - "=if(d>c,"send reminder","")
F - date reminder sent
G - "+f+14" (To give follow up call)
H - "=if(d>g,"follow up call","pending")

Now, whilst it works, its a bit too long winded, column wise. But, whilst info is known for columns A through d, column f only has data when required/inputted, so at the moment, g and h are copy and pasted once info given in f (value return otherwise)

Any ideas how to make this easier??

I would like to have E, reporting all - send reminder, pending and follow up call"

Help appreciated
 
#2
I recently posted a similar help thread and got a fantastic response, Although I am unable to help would it be possible for the MOD's to form a thread with all these little help snippets put in once place?
 
#3
I was wondering about how to do this myself, so I have just bothered my arse to figure it out.

You need something like this

=IF(D>=G,"follow up call",(IF(D>=F,"pending",IF(D>=C,"send reminder",""))))

Basically it starts with the latest event and works backwards. l also used >= (greater than or equal to) as it seems to make more sense.

This makes me geek doesn't it?
 
#4
You could use conditional formatting on the contract end date to give visual clues as to whether a reminder is due.

Post the sheet or an extract of it and I can cut it around for you.

Q
 
#5
I was wondering about how to do this myself, so I have just bothered my arse to figure it out.

You need something like this

=IF(D>=G,"follow up call",(IF(D>=F,"pending",IF(D>=C,"send reminder",""))))

Basically it starts with the latest event and works backwards. l also used >= (greater than or equal to) as it seems to make more sense.

This makes me geek doesn't it?
Yes mate. I'm an IT tech, and I even I can't be arsed with all that s**t.
 

Rod924

LE
Kit Reviewer
#7
#8
Bit of a sample here.

Option 1 - Rows 2 - 6
Your 'working date' is entered in a column, the reminder & followup are shown in different columns

Option 2 - Rows 8 - 13
Your 'working date' is always 'today' and can be embedded in the formalae to save a column, the reminder & followup are shown in different columns
(There are both a 'today()' and 'now()' function in Excel which may vary between versions)

Option 3 - Rows 8 - 13
Your 'working date' is always 'today', the reminder & followup are combined into one
(This is getting to a pretty complex formula so to be sure I have set the evaluations in the right order will need further test figures)






I have made the use of >= whereever I think appropriate.
I have assumed contract end date is one year on and used a +365 formula, you may need to enter a specific date
today() may need testing to ensure it works correctly with your Excel version, and that it always refreshes to then day that the spreadsheet is being viewed

 
#9
Oops ...
Think that last formula was wrong - if status is combined you may want 'Reminder sent / pending followup'


...and edited to actualy use the revised formula!
 

Rod924

LE
Kit Reviewer
#10
Oops ...
Think that last formula was wrong - if status is combined you may want 'Reminder sent / pending followup'




...and edited to actualy use the revised formula!
Tommilka, many,many thanks for your efforts and others too.
 
Thread starter Similar threads Forum Replies Date
Tartan_Terrier Gaming and Software 13
Spanish_Dave The NAAFI Bar 46
Taffnp Hardware - PCs, Consoles, Gadgets 14

Similar threads

Latest Threads

Top