=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]

For dates due next month, I would love for the formula to say "Due next month" as opposed to due in X months if possible!

 

[etaf]

how about
=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"

Get days, months, and years between dates

To calculate and display the time between two dates in days, months, and years, you can use a formula based on the DATEDIF function. In the example shown, the formula in E5 is: =DATEDIF(B5,C5,"y")&" years, "&DATEDIF(B5,C5,"ym")&" months, " &DATEDIF(B5,C5,"md")&" days" where start dates are in...

exceljet.net

Book5
	A	B	C	D	E	F	G	H
1						deadline
2						8/1/24	0 years, 0 months, 7 days
3						9/12/24	0 years, 1 months, 18 days
4						10/24/24	0 years, 2 months, 29 days
5						12/5/24	0 years, 4 months, 10 days
6						1/16/25	0 years, 5 months, 22 days
7						2/27/25	0 years, 7 months, 2 days
8						4/10/25	0 years, 8 months, 16 days
9
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"

Cells with Data Validation
Cell	Allow	Criteria
C2	Custom	=IF(B2<>"Disallowed","N/A",register!$B$7:$B$12)

 

[etaf]

so today is 25 july
do you want due next month - when the date is 25 aug
or when its anytime in Aug

=IF(DATEDIF(TODAY(),F2,"ym")=1,"due next month",DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days")

Book5
	A	B	C	D	E	F	G	H
1						deadline
2						8/1/24	0 years, 0 months, 7 days	0 years, 0 months, 7 days
3						9/12/24	0 years, 1 months, 18 days	due next month
4						10/24/24	0 years, 2 months, 29 days	0 years, 2 months, 29 days
5						12/5/24	0 years, 4 months, 10 days	0 years, 4 months, 10 days
6						1/16/25	0 years, 5 months, 22 days	0 years, 5 months, 22 days
7						2/27/25	0 years, 7 months, 2 days	0 years, 7 months, 2 days
8						4/10/25	0 years, 8 months, 16 days	0 years, 8 months, 16 days
9						7/27/24	0 years, 0 months, 2 days	0 years, 0 months, 2 days
10						7/29/24	0 years, 0 months, 4 days	0 years, 0 months, 4 days
11						7/31/24	0 years, 0 months, 6 days	0 years, 0 months, 6 days
12						8/2/24	0 years, 0 months, 8 days	0 years, 0 months, 8 days
13						8/4/24	0 years, 0 months, 10 days	0 years, 0 months, 10 days
14						8/6/24	0 years, 0 months, 12 days	0 years, 0 months, 12 days
15						8/8/24	0 years, 0 months, 14 days	0 years, 0 months, 14 days
16						8/10/24	0 years, 0 months, 16 days	0 years, 0 months, 16 days
17						8/12/24	0 years, 0 months, 18 days	0 years, 0 months, 18 days
18						8/14/24	0 years, 0 months, 20 days	0 years, 0 months, 20 days
19						8/16/24	0 years, 0 months, 22 days	0 years, 0 months, 22 days
20						8/18/24	0 years, 0 months, 24 days	0 years, 0 months, 24 days
21						8/20/24	0 years, 0 months, 26 days	0 years, 0 months, 26 days
22						8/22/24	0 years, 0 months, 28 days	0 years, 0 months, 28 days
23						8/24/24	0 years, 0 months, 30 days	0 years, 0 months, 30 days
24						8/25/24	0 years, 1 months, 0 days	due next month
25						8/28/24	0 years, 1 months, 3 days	due next month
26						8/30/24	0 years, 1 months, 5 days	due next month
27						9/1/24	0 years, 1 months, 7 days	due next month
28						9/3/24	0 years, 1 months, 9 days	due next month
29						9/5/24	0 years, 1 months, 11 days	due next month
30						9/7/24	0 years, 1 months, 13 days	due next month
31						9/9/24	0 years, 1 months, 15 days	due next month
32						9/11/24	0 years, 1 months, 17 days	due next month
33						9/13/24	0 years, 1 months, 19 days	due next month
34						9/15/24	0 years, 1 months, 21 days	due next month
35						9/17/24	0 years, 1 months, 23 days	due next month
36						9/19/24	0 years, 1 months, 25 days	due next month
37						9/21/24	0 years, 1 months, 27 days	due next month
38						9/23/24	0 years, 1 months, 29 days	due next month
39						9/25/24	0 years, 2 months, 0 days	0 years, 2 months, 0 days
Sheet1

Cell Formulas
Range	Formula
G2:G39	G2	=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"
H2:H39	H2	=IF(DATEDIF(TODAY(),F2,"ym")=1,"due next month",DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days")

Cells with Data Validation
Cell	Allow	Criteria
C2	Custom	=IF(B2<>"Disallowed","N/A",register!$B$7:$B$12)

 

[Lauren22newell]

You are so helpful, thank you for this! I don't mind whole months for the text, so if something is due on 1 August and today is 25 July, I would be happy for it to say "due next month" which would change to "this month" once in August if this makes sense? This spreadsheet is going to help so much with managing my portfolio, so thank you for your help so far, it is really appreciated!

 

[etaf]

can we have some more examples , so i can see what you want for each month

as we are in JULY ,
any date in F with a July date is "this Month"
any date in F with a August date is "Due next Month"
Sept - due in 2 months or X days
Oct - due in 3 months or X days

will the dates entered include Next year
July-25 ???

=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(MONTH(F2)-MONTH(TODAY())=1,"Due Next Month",f2-today()))

BUT , as you can see does not include the year column J

Book5
	A	B	C	D	E	F	G	H	I	J
1						deadline
2						8/1/24	0 years, 0 months, 7 days	0 years, 0 months, 7 days		Due Next Month
3						9/12/24	0 years, 1 months, 18 days	due next month		49
4						10/24/24	0 years, 2 months, 29 days	0 years, 2 months, 29 days		91
5						12/5/24	0 years, 4 months, 10 days	0 years, 4 months, 10 days		133
6						1/16/25	0 years, 5 months, 22 days	0 years, 5 months, 22 days		175
7						2/27/25	0 years, 7 months, 2 days	0 years, 7 months, 2 days		217
8						4/10/25	0 years, 8 months, 16 days	0 years, 8 months, 16 days		259
9						7/27/24	0 years, 0 months, 2 days	0 years, 0 months, 2 days		Due This Month
10						7/29/24	0 years, 0 months, 4 days	0 years, 0 months, 4 days		Due This Month
11						7/31/24	0 years, 0 months, 6 days	0 years, 0 months, 6 days		Due This Month
12						8/2/24	0 years, 0 months, 8 days	0 years, 0 months, 8 days		Due Next Month
13						8/4/24	0 years, 0 months, 10 days	0 years, 0 months, 10 days		Due Next Month
14						8/6/24	0 years, 0 months, 12 days	0 years, 0 months, 12 days		Due Next Month
15						8/8/24	0 years, 0 months, 14 days	0 years, 0 months, 14 days		Due Next Month
16						8/10/24	0 years, 0 months, 16 days	0 years, 0 months, 16 days		Due Next Month
17						8/12/24	0 years, 0 months, 18 days	0 years, 0 months, 18 days		Due Next Month
18						8/14/24	0 years, 0 months, 20 days	0 years, 0 months, 20 days		Due Next Month
19						8/16/24	0 years, 0 months, 22 days	0 years, 0 months, 22 days		Due Next Month
20						8/18/24	0 years, 0 months, 24 days	0 years, 0 months, 24 days		Due Next Month
21						8/20/24	0 years, 0 months, 26 days	0 years, 0 months, 26 days		Due Next Month
22						8/22/24	0 years, 0 months, 28 days	0 years, 0 months, 28 days		Due Next Month
23						8/24/24	0 years, 0 months, 30 days	0 years, 0 months, 30 days		Due Next Month
24						8/25/24	0 years, 1 months, 0 days	due next month		Due Next Month
25						8/28/24	0 years, 1 months, 3 days	due next month		Due Next Month
26						8/30/24	0 years, 1 months, 5 days	due next month		Due Next Month
27						9/1/24	0 years, 1 months, 7 days	due next month		38
28						9/3/24	0 years, 1 months, 9 days	due next month		40
29						9/5/24	0 years, 1 months, 11 days	due next month		42
30						9/7/24	0 years, 1 months, 13 days	due next month		44
31						9/9/24	0 years, 1 months, 15 days	due next month		46
32						9/11/24	0 years, 1 months, 17 days	due next month		48
33						9/13/24	0 years, 1 months, 19 days	due next month		50
34						9/15/24	0 years, 1 months, 21 days	due next month		52
35						9/17/24	0 years, 1 months, 23 days	due next month		54
36						9/19/24	0 years, 1 months, 25 days	due next month		56
37						9/21/24	0 years, 1 months, 27 days	due next month		58
38						9/23/24	0 years, 1 months, 29 days	due next month		60
39						9/25/24	0 years, 2 months, 0 days	0 years, 2 months, 0 days		62
Sheet1

Cell Formulas
Range	Formula
G2:G39	G2	=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"
H2:H39	H2	=IF(DATEDIF(TODAY(),F2,"ym")=1,"due next month",DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days")
J2:J39	J2	=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(MONTH(F2)-MONTH(TODAY())=1,"Due Next Month",F2-TODAY()))

Cells with Data Validation
Cell	Allow	Criteria
C2	Custom	=IF(B2<>"Disallowed","N/A",register!$B$7:$B$12)

 

[Lauren22newell]

I've tried to snip my spreadsheet to show the example of what I'm doing on this section - I've got the year end date which is unique to each client, and then filing deadline which is a basic formula adding 9 months to the date in column D. Then I have your formula showing the number of years/months/days to the deadline.

Ideally, in column G, I would like a formula like those you have posted above to say "due this month/due next month/due in x months" - I don't mind not having one for next year as the months will be helpful for that and it will never be much more than a year anyway.

I hope this makes sense!

Thank you

 

[etaf]

=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(MONTH(F2)-MONTH(TODAY())=1,"Due Next Month",DATEDIF(TODAY(),F2,"ym")&" month"))
EDIT maybe NOT , as 1 month , should be 2 months - i'll think

does that work , if not over a year then it wont work as Aug 2024 and Aug 2025 will still be seen as month 8 and so say 'Due this Month"

Book5
	F	G
1	deadline
2	8/1/24	Due Next Month
3	9/12/24	1 month
4	10/24/24	2 month
5	12/5/24	4 month
6	1/16/25	5 month
7	2/27/25	7 month
8	4/10/25	8 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	9/1/24	1 month
28	9/3/24	1 month
29	9/5/24	1 month
30	9/7/24	1 month
31	9/9/24	1 month
32	9/11/24	1 month
33	9/13/24	1 month
34	9/15/24	1 month
35	9/17/24	1 month
36	9/19/24	1 month
37	9/21/24	1 month
38	9/23/24	1 month
39	9/25/24	2 month
Sheet1

Cell Formulas
Range	Formula
G2:G39	G2	=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(MONTH(F2)-MONTH(TODAY())=1,"Due Next Month",DATEDIF(TODAY(),F2,"ym")&" month"))

 

[etaf]

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

I feel this is way overcomplicated

Book5
	F	G
1	deadline
2	8/1/24	Due Next Month 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 month
13	8/4/24	Due Next Month month
14	8/6/24	Due Next Month month
15	8/8/24	Due Next Month month
16	8/10/24	Due Next Month month
17	8/12/24	Due Next Month month
18	8/14/24	Due Next Month month
19	8/16/24	Due Next Month month
20	8/18/24	Due Next Month month
21	8/20/24	Due Next Month month
22	8/22/24	Due Next Month month
23	8/24/24	Due Next Month month
24	8/25/24	Due Next Month month
25	8/28/24	Due Next Month month
26	8/30/24	Due Next Month month
27	9/1/24	2 month
28	9/3/24	2 month
29	9/5/24	2 month
30	9/7/24	2 month
31	9/9/24	2 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(MONTH(F2)-MONTH(TODAY())=1,"Due Next Month",IF(MONTH(F2)>MONTH(TODAY()),MONTH(F2)-MONTH(TODAY()),12-MONTH(TODAY())+MONTH(F2)))&" month")

 

[etaf]

Actually that will not work for due next month when current month is december and next month is Jan
will look at