=IF formula

[Lauren22newell]

Hi all,

I'm new here but have been working on a spreadsheet to help me with managing a new client list over the past few days and have found this forum so helpful.

I have imagined a formula in my head that I'd find really helpful to use but I'm not sure how to go about it.

In column F, I have calculated a deadline day for filing accounts. In column G, I'd like to have a formula that picks out how many days or months between that deadline and today, and shows up as "Due in X days or X months" - Is this possible? I've done one I found on here for deadlines in the current month, so it shows "due this month" but I'm not sure how to go about this one.

Any help would be much appreciated. Thank you!

 

[Lauren22newell]

Thank you so much for your help on this, I really appreciate it! I wish I had the skill to know how to write them but I'm trying to read the ones you've sent and pick up on them!

 

[Alex Blakenburg]

Building on @etaf's work, this might be a bit longer but might be simpler and will work across years.
PS: I would suggest putting TODAY() in a cell that you reference and not in a formula.
It will give you the flexibility to backdate the date if you need to and try different dates when you are testing.

In G2

=LET(mths,(YEAR(F2)-YEAR(TODAY()))*12+(MONTH(F2)-MONTH(TODAY())),
     SWITCH(mths,
       0,"Due This Month",
       1,"Due Next Month",
       2,"Due in 1 Month",
       "Due in " & mths & " Months"))

 

[Lauren22newell]

Hi Alex,

This looks great, thank you for taking the time to build on @etaf's help. I have pasted this into my column G2 but it is throwing out #NAME - Screenshot below.

Sorry, I am a bit lost with the TODAY() bit - Please could you explain? Promise I'm not a silly person but still very much learning on Excel!

 

[etaf]

Alex Blakenburg​

Has a better solution , although that is 1 month out , as i was , Sept saying 1 month Oct saying 2 months, is that what you want , I changed to say 2 months for sept

as it was getting complicated with nested IF's

However for completeness , this will take into account now if today is december and F2 is Jan ,
BUT will NOT take into account Years
So aug 25 , will be seen as next month


=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(OR(MONTH(F2)-MONTH(TODAY())=1,MONTH(F2)-MONTH(TODAY())=-11),"Due Next Month",
IF(MONTH(F2)>MONTH(TODAY()),MONTH(F2)-MONTH(TODAY())&" month",
12-MONTH(TODAY())+MONTH(F2)&" month")))

Book5
	F	G
1	deadline
2	8/1/24	Due Next Month
3	9/12/24	2 month
4	10/24/24	3 month
5	12/5/24	5 month
6	1/16/25	6 month
7	2/27/25	7 month
8	4/10/25	9 month
9	7/27/24	Due This Month
10	7/29/24	Due This Month
11	7/31/24	Due This Month
12	8/2/24	Due Next Month
13	8/4/24	Due Next Month
14	8/6/24	Due Next Month
15	8/8/24	Due Next Month
16	8/10/24	Due Next Month
17	8/12/24	Due Next Month
18	8/14/24	Due Next Month
19	8/16/24	Due Next Month
20	8/18/24	Due Next Month
21	8/20/24	Due Next Month
22	8/22/24	Due Next Month
23	8/24/24	Due Next Month
24	8/25/24	Due Next Month
25	8/28/24	Due Next Month
26	8/30/24	Due Next Month
27	7/1/25	Due This Month
28	8/3/25	Due Next Month
29	9/5/24	2 month
30	10/7/24	3 month
31	11/9/24	4 month
32	9/11/24	2 month
33	9/13/24	2 month
34	9/15/24	2 month
35	9/17/24	2 month
36	9/19/24	2 month
37	9/21/24	2 month
38	9/23/24	2 month
39	9/25/24	2 month
Sheet1

Cell Formulas
Range	Formula
G2:G39	G2	=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(OR(MONTH(F2)-MONTH(TODAY())=1,MONTH(F2)-MONTH(TODAY())=-11),"Due Next Month", IF(MONTH(F2)>MONTH(TODAY()),MONTH(F2)-MONTH(TODAY())&" month", 12-MONTH(TODAY())+MONTH(F2)&" month")))

with Alex solution

Book5
	F	G	H
1	deadline
2	8/1/24	Due Next Month	Due Next Month
3	9/12/24	2 month	Due in 1 Month
4	10/24/24	3 month	Due in 3 Months
5	12/5/24	5 month	Due in 5 Months
6	1/16/25	6 month	Due in 6 Months
7	2/27/25	7 month	Due in 7 Months
8	4/10/25	9 month	Due in 9 Months
9	7/27/24	Due This Month	Due This Month
10	7/29/24	Due This Month	Due This Month
11	7/31/24	Due This Month	Due This Month
12	8/2/24	Due Next Month	Due Next Month
13	8/4/24	Due Next Month	Due Next Month
14	8/6/24	Due Next Month	Due Next Month
15	8/8/24	Due Next Month	Due Next Month
16	8/10/24	Due Next Month	Due Next Month
17	8/12/24	Due Next Month	Due Next Month
18	8/14/24	Due Next Month	Due Next Month
19	8/16/24	Due Next Month	Due Next Month
20	8/18/24	Due Next Month	Due Next Month
21	8/20/24	Due Next Month	Due Next Month
22	8/22/24	Due Next Month	Due Next Month
23	8/24/24	Due Next Month	Due Next Month
24	8/25/24	Due Next Month	Due Next Month
25	8/28/24	Due Next Month	Due Next Month
26	8/30/24	Due Next Month	Due Next Month
27	7/1/25	Due This Month	Due in 12 Months
28	8/3/25	Due Next Month	Due in 13 Months
29	9/5/24	2 month	Due in 1 Month
30	10/7/24	3 month	Due in 3 Months
31	11/9/24	4 month	Due in 4 Months
32	9/11/24	2 month	Due in 1 Month
33	9/13/24	2 month	Due in 1 Month
34	9/15/24	2 month	Due in 1 Month
35	9/17/24	2 month	Due in 1 Month
36	9/19/24	2 month	Due in 1 Month
37	9/21/24	2 month	Due in 1 Month
38	9/23/24	2 month	Due in 1 Month
39	9/25/24	2 month	Due in 1 Month
Sheet1

Cell Formulas
Range	Formula
G2:G39	G2	=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(OR(MONTH(F2)-MONTH(TODAY())=1,MONTH(F2)-MONTH(TODAY())=-11),"Due Next Month", IF(MONTH(F2)>MONTH(TODAY()),MONTH(F2)-MONTH(TODAY())&" month", 12-MONTH(TODAY())+MONTH(F2)&" month")))
H2:H39	H2	=LET(mths,(YEAR(F2)-YEAR(TODAY()))*12+(MONTH(F2)-MONTH(TODAY())), SWITCH(mths, 0,"Due This Month", 1,"Due Next Month", 2,"Due in 1 Month", "Due in " & mths & " Months"))

 

[Lauren22newell]

You are all brilliant and so patient and helpful! This has worked and I now have a due column that works!!! :D

 

[Alex Blakenburg]

Good catch by etaf, I made a couple of errors.
You need to copy it straight into the "Formula box" of G2 or H2

=LET(mths,(YEAR(F2)-YEAR(TODAY()))*12+(MONTH(F2)-MONTH(TODAY())),
     SWITCH(mths,
       0,"Due This Month",
       1,"Due Next Month",
       "Due in " & mths & " Months"))

 

[Lauren22newell]

This forum is brill! Thank you again, have a great weekend when it comes!

 

[etaf]

you are very welcome

Book5
	F	G	H
1	deadline
2	8/1/24	Due Next Month	Due Next Month
3	9/12/24	2 month	Due in 2 Months
4	10/24/24	3 month	Due in 3 Months
5	12/5/24	5 month	Due in 5 Months
6	1/16/25	6 month	Due in 6 Months
7	2/27/25	7 month	Due in 7 Months
8	4/10/25	9 month	Due in 9 Months
9	7/27/24	Due This Month	Due This Month
10	7/29/24	Due This Month	Due This Month
11	7/31/24	Due This Month	Due This Month
12	8/2/24	Due Next Month	Due Next Month
13	8/4/24	Due Next Month	Due Next Month
14	8/6/24	Due Next Month	Due Next Month
15	8/8/24	Due Next Month	Due Next Month
16	8/10/24	Due Next Month	Due Next Month
17	8/12/24	Due Next Month	Due Next Month
18	8/14/24	Due Next Month	Due Next Month
19	8/16/24	Due Next Month	Due Next Month
20	8/18/24	Due Next Month	Due Next Month
21	8/20/24	Due Next Month	Due Next Month
22	8/22/24	Due Next Month	Due Next Month
23	8/24/24	Due Next Month	Due Next Month
24	8/25/24	Due Next Month	Due Next Month
25	8/28/24	Due Next Month	Due Next Month
26	8/30/24	Due Next Month	Due Next Month
27	7/1/25	Due This Month	Due in 12 Months
28	8/3/25	Due Next Month	Due in 13 Months
29	9/5/24	2 month	Due in 2 Months
30	10/7/24	3 month	Due in 3 Months
31	11/9/24	4 month	Due in 4 Months
32	9/11/24	2 month	Due in 2 Months
33	9/13/24	2 month	Due in 2 Months
34	9/15/24	2 month	Due in 2 Months
35	9/17/24	2 month	Due in 2 Months
36	9/19/24	2 month	Due in 2 Months
37	9/21/24	2 month	Due in 2 Months
38	9/23/24	2 month	Due in 2 Months
39	9/25/24	2 month	Due in 2 Months
Sheet1

Cell Formulas
Range	Formula
G2:G39	G2	=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(OR(MONTH(F2)-MONTH(TODAY())=1,MONTH(F2)-MONTH(TODAY())=-11),"Due Next Month", IF(MONTH(F2)>MONTH(TODAY()),MONTH(F2)-MONTH(TODAY())&" month", 12-MONTH(TODAY())+MONTH(F2)&" month")))
H2:H39	H2	=LET(mths,(YEAR(F2)-YEAR(TODAY()))*12+(MONTH(F2)-MONTH(TODAY())), SWITCH(mths, 0,"Due This Month", 1,"Due Next Month", "Due in " & mths & " Months"))