0
   

Excel Problem

 
 
Reply Sat 24 Jan, 2015 05:14 pm
I have a problem with Excel ....
I need to see how many days are left out of 30,
I sent a contract out, they have 30 days to send it back
I need to send them a reminder at 5 days before their time runs out
  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Question • Score: 0 • Views: 1,736 • Replies: 29
No top replies

 
jespah
 
  2  
Reply Sat 24 Jan, 2015 05:27 pm
@DaughterRuler,
Bold items are to be typed exactly. It'll be easiest if you just copy/paste.

  1. Open a new Excel tab
  2. In cel A1, type Due Date
  3. In cel B1, type Today
  4. In cel C1, type Difference
  5. In cel D1, type Reminder?
  6. In cel A2, type whatever the due date is, e. g. 3/3/15
  7. In cel B2, type =NOW()
  8. In cel C2, type =A2-B2
  9. In cel D2, type =IF(C2>6,"","send reminder")

You can test the formula by changing values in cel A2. If the difference between the two dates is 5 or less, the words send reminder will appear in cel D2. I realize the formula looks wrong; you'd think it should say C2>5, but I have tested this and it works.
DaughterRuler
 
  2  
Reply Sat 24 Jan, 2015 05:58 pm
@jespah,
Jespah:
Oh my gosh, I've been working on this for over a week! If I may ask ....Is there a way I can see in one of the columns 5 days, then if I go back in 2 days later it will say 3 days or even a rundown of the 30 days

DR
jespah
 
  2  
Reply Sat 24 Jan, 2015 06:15 pm
@DaughterRuler,
The NOW formula gives you today's date. So, tomorrow, it'll say 1/25/15 and the results will update accordingly.

I'm not sure what you mean by a rundown of the 30 days. Are you looking for something like a countdown of there's only 3 days left until you need to send out a note and yell at somebody? Wink
DaughterRuler
 
  1  
Reply Sat 24 Jan, 2015 06:17 pm
@jespah,
Yes!
0 Replies
 
DaughterRuler
 
  1  
Reply Sat 24 Jan, 2015 06:41 pm
@jespah,
What I guess I want to see is actually "5 days out - Send Reminder" when I go in the next day it would say 4 days out - Send Reminder. If the time has elapsed it would say "EXPIRED". All it says now is "Send Reminder"
jespah
 
  2  
Reply Sat 24 Jan, 2015 06:48 pm
@DaughterRuler,
Here's part of it. You can change step #9 as follows:

In cel D2, type =IF(C2>6,"","EXPIRED")

For the countdown, one thing you can do is change step #8 as follows:
In cel C2, type =CONCATENATE((A2-B2)," days out")
jespah
 
  2  
Reply Sat 24 Jan, 2015 06:49 pm
@jespah,
But it's not perfect; it will get you decimal places in your number of remaining days. Is that okay?

Actually, I just broke it. Hang on; I'll fix it.
jespah
 
  2  
Reply Sat 24 Jan, 2015 06:53 pm
@jespah,
The thing is, I don't like my new column. What I just did is, I added a new column between C and D. This is the new column D (e. g. cel E1 now says Reminder?).

Then I added the following formula to cel D2:
=CONCATENATE((C2)," days out")

Kinda ugly and I'm not so sure it helps you too much.
DaughterRuler
 
  2  
Reply Sat 24 Jan, 2015 07:49 pm
@jespah,
Jespah:
Thank you sooooooo much.
I appreciate you! I put a Conditional Format Rule in so the Send Reminder cell is Red!

Blessings to you!
jespah
 
  1  
Reply Sun 25 Jan, 2015 06:12 am
@DaughterRuler,
Oooh smart. My pleasure. Ask more Excel qs if you like. I just realized that in April I will be using the program for (egad) 20 years. I use it pretty much every day although I confess I never got the hang of VB code.
DaughterRuler
 
  1  
Reply Mon 26 Jan, 2015 12:30 pm
@jespah,
OK, here's an easy one for you, but I can't get it to work ....
I have the Contract date in column C
The Return date in column D =c5=30
Today in column E
How many days left in column F =IF(C9,"", D9-E9)
How many days out in Column G =IF(F5>6,"","Days Out")
Call Client in Column H =IF(F5>6,"","Follow-up ~ Call Client")
The problem ...
If I don't have a date in Column C - I don't want to see anything in Column F
So I put =if(c5,"",D5-E5)
it gives me a -42001 instead of leaving the cell blank.
jespah
 
  1  
Reply Mon 26 Jan, 2015 01:08 pm
@DaughterRuler,
Huh. I wish I could see this - can you grab a screen shot and copy the image to Photobucket, then post it here? That might help. I have class work and workity work to do, will try to be back later, assuming we still have power during Snowmaggedon.
DaughterRuler
 
  1  
Reply Mon 26 Jan, 2015 01:25 pm
@jespah,
I don't have photobucket

jespah
 
  1  
Reply Mon 26 Jan, 2015 01:39 pm
@DaughterRuler,
It's a free website for posting images: http://s147.photobucket.com/

If you don't like it, there are others. You can also post images on Facebook but you need to get the URL correct and the privacy permissions have to be set for people to be able to see any images.
DaughterRuler
 
  1  
Reply Mon 26 Jan, 2015 02:00 pm
@jespah,
I can't drag & drop .... I got on photobucket, did copy but there is no paste
0 Replies
 
DaughterRuler
 
  1  
Reply Tue 27 Jan, 2015 08:10 am
@jespah,
Hi Jespah,
How high is the snow?
Did you get a chance to look at my problem?
0 Replies
 
jespah
 
  1  
Reply Tue 27 Jan, 2015 10:47 am
20" here and still coming down. Haven't looked, sorry.
timur
 
  2  
Reply Tue 27 Jan, 2015 12:44 pm
@jespah,
Don't worry and stay warm, Jespah.

DaughterRuler is probably looking for this:

http://i62.tinypic.com/25qvyxd.jpg
jespah
 
  1  
Reply Tue 27 Jan, 2015 03:01 pm
@timur,
Oh, you're a love. Thanks, timur.
0 Replies
 
 

Related Topics

 
  1. Forums
  2. » Excel Problem
Copyright © 2020 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.03 seconds on 02/24/2020 at 09:52:12