+ Reply to Thread
Results 1 to 12 of 12

Formula for next due date?

  1. #1
    Registered User
    Join Date
    01-17-2021
    Location
    Norwich, New York
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Formula for next due date?

    Hey all,

    I'm new to this forum and relatively inexperienced with Excel. I've learned a lot by brute force over the years, but this one has me stumped as I am honing my skills a little more each day. Finance and dates are a little different for me.

    I would like a cell to display the next due date, based on a day of the month entered within the cell. For example, if a bill is to be paid on the 6th of each month, I would enter "6" but I would like it to display, 6FEB2021 (as of today). After the 6th of February, it would automatically change to 6MAR2021. Of course, it gets trickier at the end of the year, where after the 6th of December, the next due date will be 6JAN2022.

    Ultimately, I want to be able to refer to this cell using conditional formatting to highlight green when a payment is made before the date, yellow if a payment is not made before the date, and red if the current date matches the next payment date. Everything I am doing right now for my finances is more of an experiment, as a I said to hone my skills, but I hope that the end result is a spreadsheet I can give my stepdaughter for her own finances in the future.

    What is the best way of going about this? Will I need to create a table and use EDATE?

    Thanks,

    Matt in Norwich
    Attached Files Attached Files
    Last edited by MortallyWounded; 01-17-2021 at 12:52 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for next due date?

    Welcome to the forum.

    Your Excel version would appear to be 365 - please update your user profile accordingly.

    Not tricky with EDATE, as long as your dates are real dates, nor is the conditional formatting hard.

    There are instructions at the top of the page explaining how to attach your sample workbook - this will help us to help you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-17-2021
    Location
    Norwich, New York
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Formula for next due date?

    Ali:

    I've uploaded an example. Row 2 is how I currently look at the upcoming date; I'd like it to change to reflect the upcoming date based on the day inputted here. Row 12 is manually entered each time a payment is made. I think I would like it to highlight red if that date plus one month is greater than or equal to the date in Row 2.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for next due date?

    I have seen your attachment - I do not see where you have mocked up what you want. Please post again to this thread with an updated workbook.

    Row 2 is how I currently look at the upcoming date; I'd like it to change to reflect the upcoming date based on the day inputted here.
    Show me what you mean. This is ambiguous to me.
    Last edited by AliGW; 01-17-2021 at 01:01 PM.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for next due date?

    One way:

    =IF(DAY(TODAY())>B3,EOMONTH(TODAY(),0)+B3,EOMONTH(TODAY(),-1)+B3)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    01-17-2021
    Location
    Norwich, New York
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Formula for next due date?

    Thanks Glenn, that is pretty close to what I was hoping to see. I was afraid I would need to use some form of EDATE. But it isn't that bad.

    I was almost there myself, but my formula was just a little off. Thanks!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for next due date?

    What's wrong with EDATE???

    Please update your forum profile as I requested earlier. Thanks.

  8. #8
    Registered User
    Join Date
    01-17-2021
    Location
    Norwich, New York
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Formula for next due date?

    Well, there's nothing "wrong" with it, but I was hoping that maybe there was possibly a way to put it into a custom format.

    As far as the profile goes, give me a few minutes please. Sheesh!
    Last edited by AliGW; 01-17-2021 at 01:24 PM. Reason: PLEASE don't quote unnecessarily!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for next due date?

    Ooh - temper!!!

    Joking apart, you can put EDATE or EOMONTH into a CF formula if you need to.

  10. #10
    Registered User
    Join Date
    01-17-2021
    Location
    Norwich, New York
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Formula for next due date?

    Holy cow, this is going to be one of these types of forums, isn't it?

    Man I was really hoping to be able to find a forum where everyone was cool, and nobody ever injected attitude into simple answers. But no. We have a moderator who objects to being called out for having a grumpy attitude and edits people's posts, insists that everyone be up to their level of navigation skills, and gets irritated when their demands aren't immediately met. You know what? I'm not even sure it is Office 365. I'm pretty sure this thing's got a cracked Office 365 key with Home and Student 2016 products installed. So how would you like me to answer?

    I am eternally grateful for Glenn's answer. Just a quick, one-response solution that didn't cop a fuss. Kudos to you, Glenn!

    Ali, if it is easier, please just kick me off this forum instead. Ipswich, eh? My family was originally from Kent, with other family from Somerset. No wonder they left the island back in the 1850s.
    Last edited by AliGW; 01-17-2021 at 06:05 PM. Reason: Spelling ...

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for next due date?

    I'll say nowt.... other than... you're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for next due date?

    LOL! Glad to see you’ve got a sense of humour.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-21-2018, 09:25 AM
  2. Replies: 7
    Last Post: 02-02-2017, 02:41 PM
  3. Replies: 5
    Last Post: 12-01-2015, 02:36 PM
  4. [SOLVED] Formula to compare date (including month and year) from a listed date to today's date
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 05:31 PM
  5. Replies: 2
    Last Post: 11-03-2014, 10:29 AM
  6. [SOLVED] Help in writing formula in excel to produce dates based on start date and end date
    By shoot for moon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2014, 01:00 PM
  7. [SOLVED] Formula assistance needed to sum data based on anniversary date and month date
    By lilsnoop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2012, 11:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1