=IF formula

L

Lauren22newell

New Member
Joined
Jul 25, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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!
 
Upvote 0
how about
=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"

exceljet.net

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 exceljet.net

Book5
ABCDEFGH
1deadline
28/1/240 years, 0 months, 7 days
39/12/240 years, 1 months, 18 days
410/24/240 years, 2 months, 29 days
512/5/240 years, 4 months, 10 days
61/16/250 years, 5 months, 22 days
72/27/250 years, 7 months, 2 days
84/10/250 years, 8 months, 16 days
9
Sheet1
Cell Formulas
RangeFormula
G2:G8G2=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"
Cells with Data Validation
CellAllowCriteria
C2Custom=IF(B2<>"Disallowed","N/A",register!$B$7:$B$12)
 
Upvote 0
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
ABCDEFGH
1deadline
28/1/240 years, 0 months, 7 days0 years, 0 months, 7 days
39/12/240 years, 1 months, 18 daysdue next month
410/24/240 years, 2 months, 29 days0 years, 2 months, 29 days
512/5/240 years, 4 months, 10 days0 years, 4 months, 10 days
61/16/250 years, 5 months, 22 days0 years, 5 months, 22 days
72/27/250 years, 7 months, 2 days0 years, 7 months, 2 days
84/10/250 years, 8 months, 16 days0 years, 8 months, 16 days
97/27/240 years, 0 months, 2 days0 years, 0 months, 2 days
107/29/240 years, 0 months, 4 days0 years, 0 months, 4 days
117/31/240 years, 0 months, 6 days0 years, 0 months, 6 days
128/2/240 years, 0 months, 8 days0 years, 0 months, 8 days
138/4/240 years, 0 months, 10 days0 years, 0 months, 10 days
148/6/240 years, 0 months, 12 days0 years, 0 months, 12 days
158/8/240 years, 0 months, 14 days0 years, 0 months, 14 days
168/10/240 years, 0 months, 16 days0 years, 0 months, 16 days
178/12/240 years, 0 months, 18 days0 years, 0 months, 18 days
188/14/240 years, 0 months, 20 days0 years, 0 months, 20 days
198/16/240 years, 0 months, 22 days0 years, 0 months, 22 days
208/18/240 years, 0 months, 24 days0 years, 0 months, 24 days
218/20/240 years, 0 months, 26 days0 years, 0 months, 26 days
228/22/240 years, 0 months, 28 days0 years, 0 months, 28 days
238/24/240 years, 0 months, 30 days0 years, 0 months, 30 days
248/25/240 years, 1 months, 0 daysdue next month
258/28/240 years, 1 months, 3 daysdue next month
268/30/240 years, 1 months, 5 daysdue next month
279/1/240 years, 1 months, 7 daysdue next month
289/3/240 years, 1 months, 9 daysdue next month
299/5/240 years, 1 months, 11 daysdue next month
309/7/240 years, 1 months, 13 daysdue next month
319/9/240 years, 1 months, 15 daysdue next month
329/11/240 years, 1 months, 17 daysdue next month
339/13/240 years, 1 months, 19 daysdue next month
349/15/240 years, 1 months, 21 daysdue next month
359/17/240 years, 1 months, 23 daysdue next month
369/19/240 years, 1 months, 25 daysdue next month
379/21/240 years, 1 months, 27 daysdue next month
389/23/240 years, 1 months, 29 daysdue next month
399/25/240 years, 2 months, 0 days0 years, 2 months, 0 days
Sheet1
Cell Formulas
RangeFormula
G2:G39G2=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"
H2:H39H2=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
CellAllowCriteria
C2Custom=IF(B2<>"Disallowed","N/A",register!$B$7:$B$12)
 
Upvote 0
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!
 
Upvote 0
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
ABCDEFGHIJ
1deadline
28/1/240 years, 0 months, 7 days0 years, 0 months, 7 daysDue Next Month
39/12/240 years, 1 months, 18 daysdue next month49
410/24/240 years, 2 months, 29 days0 years, 2 months, 29 days91
512/5/240 years, 4 months, 10 days0 years, 4 months, 10 days133
61/16/250 years, 5 months, 22 days0 years, 5 months, 22 days175
72/27/250 years, 7 months, 2 days0 years, 7 months, 2 days217
84/10/250 years, 8 months, 16 days0 years, 8 months, 16 days259
97/27/240 years, 0 months, 2 days0 years, 0 months, 2 daysDue This Month
107/29/240 years, 0 months, 4 days0 years, 0 months, 4 daysDue This Month
117/31/240 years, 0 months, 6 days0 years, 0 months, 6 daysDue This Month
128/2/240 years, 0 months, 8 days0 years, 0 months, 8 daysDue Next Month
138/4/240 years, 0 months, 10 days0 years, 0 months, 10 daysDue Next Month
148/6/240 years, 0 months, 12 days0 years, 0 months, 12 daysDue Next Month
158/8/240 years, 0 months, 14 days0 years, 0 months, 14 daysDue Next Month
168/10/240 years, 0 months, 16 days0 years, 0 months, 16 daysDue Next Month
178/12/240 years, 0 months, 18 days0 years, 0 months, 18 daysDue Next Month
188/14/240 years, 0 months, 20 days0 years, 0 months, 20 daysDue Next Month
198/16/240 years, 0 months, 22 days0 years, 0 months, 22 daysDue Next Month
208/18/240 years, 0 months, 24 days0 years, 0 months, 24 daysDue Next Month
218/20/240 years, 0 months, 26 days0 years, 0 months, 26 daysDue Next Month
228/22/240 years, 0 months, 28 days0 years, 0 months, 28 daysDue Next Month
238/24/240 years, 0 months, 30 days0 years, 0 months, 30 daysDue Next Month
248/25/240 years, 1 months, 0 daysdue next monthDue Next Month
258/28/240 years, 1 months, 3 daysdue next monthDue Next Month
268/30/240 years, 1 months, 5 daysdue next monthDue Next Month
279/1/240 years, 1 months, 7 daysdue next month38
289/3/240 years, 1 months, 9 daysdue next month40
299/5/240 years, 1 months, 11 daysdue next month42
309/7/240 years, 1 months, 13 daysdue next month44
319/9/240 years, 1 months, 15 daysdue next month46
329/11/240 years, 1 months, 17 daysdue next month48
339/13/240 years, 1 months, 19 daysdue next month50
349/15/240 years, 1 months, 21 daysdue next month52
359/17/240 years, 1 months, 23 daysdue next month54
369/19/240 years, 1 months, 25 daysdue next month56
379/21/240 years, 1 months, 27 daysdue next month58
389/23/240 years, 1 months, 29 daysdue next month60
399/25/240 years, 2 months, 0 days0 years, 2 months, 0 days62
Sheet1
Cell Formulas
RangeFormula
G2:G39G2=DATEDIF(TODAY(),F2,"y")&" years, "&DATEDIF(TODAY(),F2,"ym")&" months, " &DATEDIF(TODAY(),F2,"md")&" days"
H2:H39H2=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:J39J2=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(MONTH(F2)-MONTH(TODAY())=1,"Due Next Month",F2-TODAY()))
Cells with Data Validation
CellAllowCriteria
C2Custom=IF(B2<>"Disallowed","N/A",register!$B$7:$B$12)
 
Upvote 0
1721918112162.png


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
 
Upvote 0
=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
FG
1deadline
28/1/24Due Next Month
39/12/241 month
410/24/242 month
512/5/244 month
61/16/255 month
72/27/257 month
84/10/258 month
97/27/24Due This Month
107/29/24Due This Month
117/31/24Due This Month
128/2/24Due Next Month
138/4/24Due Next Month
148/6/24Due Next Month
158/8/24Due Next Month
168/10/24Due Next Month
178/12/24Due Next Month
188/14/24Due Next Month
198/16/24Due Next Month
208/18/24Due Next Month
218/20/24Due Next Month
228/22/24Due Next Month
238/24/24Due Next Month
248/25/24Due Next Month
258/28/24Due Next Month
268/30/24Due Next Month
279/1/241 month
289/3/241 month
299/5/241 month
309/7/241 month
319/9/241 month
329/11/241 month
339/13/241 month
349/15/241 month
359/17/241 month
369/19/241 month
379/21/241 month
389/23/241 month
399/25/242 month
Sheet1
Cell Formulas
RangeFormula
G2:G39G2=IF(MONTH(TODAY())=MONTH(F2),"Due This Month",IF(MONTH(F2)-MONTH(TODAY())=1,"Due Next Month",DATEDIF(TODAY(),F2,"ym")&" month"))
 
Upvote 0
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
FG
1deadline
28/1/24Due Next Month month
39/12/242 month
410/24/243 month
512/5/245 month
61/16/256 month
72/27/257 month
84/10/259 month
97/27/24Due This Month
107/29/24Due This Month
117/31/24Due This Month
128/2/24Due Next Month month
138/4/24Due Next Month month
148/6/24Due Next Month month
158/8/24Due Next Month month
168/10/24Due Next Month month
178/12/24Due Next Month month
188/14/24Due Next Month month
198/16/24Due Next Month month
208/18/24Due Next Month month
218/20/24Due Next Month month
228/22/24Due Next Month month
238/24/24Due Next Month month
248/25/24Due Next Month month
258/28/24Due Next Month month
268/30/24Due Next Month month
279/1/242 month
289/3/242 month
299/5/242 month
309/7/242 month
319/9/242 month
329/11/242 month
339/13/242 month
349/15/242 month
359/17/242 month
369/19/242 month
379/21/242 month
389/23/242 month
399/25/242 month
Sheet1
Cell Formulas
RangeFormula
G2:G39G2=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")
 
Upvote 0
Actually that will not work for due next month when current month is december and next month is Jan
will look at
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Help with a formula
Replies
6
Views
121
paulsolar
P
M
  • Solved
Utility Meter Reading Formulas
Replies
4
Views
975
mtbe
M
E
  • Question Question
Create cash flow from list of regular payments due
Replies
7
Views
152
HighAndWilder
H
K
  • Solved
Conditionally Format cells based on relationship to a max value
Replies
1
Views
112
Fluff
Fluff
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
281
Tetra201
Tetra201

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top