Previous Topic Next Topic

Excel "If Then" Formula

Solved/Closed
jessica2991 Posts 4 Registration date Thursday 17 July 2014 Status Member Last seen 30 July 2014 - 17 Jul 2014 à 15:50
jessica2991 Posts 4 Registration date Thursday 17 July 2014 Status Member Last seen 30 July 2014 - 30 Jul 2014 à 13:34
I am looking to create a "If Then" formula for Excel that can put an "X" in the cell if the date I am reffering to (Cell A6) is 0-5 days, next cell 6-15 days, next cell 16-30, 31-42 next cell, and in the final cell greater than 42 days. Can anyone help me come up with that formula?
Related:

5 responses

Réponse 1 / 5
Mazzaropi Posts 1983 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
17 Jul 2014 à 16:01
jessica2991, Good afternoon.

It sounds a little confuse.

Do you want this comparison to wich date?
Do you want one answer for each cell?

Please, tell us more details about your lay-out.
jessica2991 Posts 4 Registration date Thursday 17 July 2014 Status Member Last seen 30 July 2014
21 Jul 2014 à 11:02
I am trying to do an aging chart. So each cell has a given day that it was contracted on, for example A6 has a date, A7 has a date, and so on. I want to set up an aging report to see if today is 0-5 days from that date, if today is 6-15 days from that day, is today is 16-30 days from that date, if today is 31-42 days from that date, and in the final cell if today is greater than 42 days. The layout should look something like this:

0-5 Days 6-15 Days 16-30 Days 31-42 Days >42 Days
X
Réponse 2 / 5
Mazzaropi Posts 1983 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
21 Jul 2014 à 12:28
jessica2991, Good afternoon.

I did an example for you.
http://speedy.sh/yrJdZ/21-07-2014-en-kioskea-Contract-Aging-Calculus-OK.xlsx

Is it what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Réponse 3 / 5
jessica2991 Posts 4 Registration date Thursday 17 July 2014 Status Member Last seen 30 July 2014
28 Jul 2014 à 11:18
I am not able to open that file. Is there any way you can write the formula out? Thank you for your help!
Réponse 4 / 5
Mazzaropi Posts 1983 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
28 Jul 2014 à 12:49
jessica2991, Good afternoon.

I checked the link and it's OK!

A screenshot of the file:

C3 --> =IF(DATEDIF(B3,TODAY(),"d")<=5,"x","")
D3 --> =IF(AND(DATEDIF(B3,TODAY(),"d")>=6,DATEDIF(B3,TODAY(),"d")<=15),"x","")
E3 --> =IF(AND(DATEDIF(B3,TODAY(),"d")>=16,DATEDIF(B3,TODAY(),"d")<=30),"x","")
F3 --> =IF(AND(DATEDIF(B3,TODAY(),"d")>=31,DATEDIF(B3,TODAY(),"d")<=42),"x","")
G3 --> =IF(DATEDIF(B3,TODAY(),"d")>42,"x","")
Copy them down

C13 --> =CONT.SE(C3:C12;"X")

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Réponse 5 / 5
jessica2991 Posts 4 Registration date Thursday 17 July 2014 Status Member Last seen 30 July 2014
30 Jul 2014 à 13:34
Thank you very much! This was exactly what I needed!

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Convert number to spelling
Sukumar -
mrwagh85 -

28 responses