Adding 1 month to another date

Hi,

Does anyone have a formula to automatically calculate 1 month forward from a date in another cell?

I have tried =MONTH(Due1,+1) but this returns #Date expected error. (Due1 is the column and cell i am trying to add the month to.

The above would be perfect but also if there is a genius way of the above calculation only being a working day?

Thank you for anyone who can help and taking the time to assist.

Dave

Best Answer

«1

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 02/07/20

    =IF(AND(WEEKDAY(Due@row) < 7, WEEKDAY(Due@row) > 1), DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row)))

    will work at any day of the year. Let me know if it works for you

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi thank you for taking some time to look at this,

    I still seem to be pulling though weekends?

    Happy to share a sheet for you to look at if that helps?

    Dave

  • L_123
    L_123 ✭✭✭✭✭✭

    You mean if the resulting date is a weekday? Mine checked if the date in the due column was a weekday. This will post a blank if the resulting date is on a weekend.

    =IF(AND(WEEKDAY(DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row))) < 7, WEEKDAY(DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row))) > 1), DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row)), "")

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi, Yes i am getting blanks with the above formula.

    Would you have the formula to simply add a month and i will live with weekends and work around it.

    Sound crazy but i cannot get it to simply add one month!

    Appreciate your help

    Dave

  • L_123
    L_123 ✭✭✭✭✭✭

    =DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row)))

    This will return 1 month ahead. The correct version of the formula you were trying to do is

    =month(Due@row)+1

    But the issue with this formula is december

    12 + 1 does not equal 1.

    Can you tell me a little more about why my previous formula didn't fit your needs? I must be misunderstanding what output you want in relation to working days. Do you want it to return the next monday if the true value is on the weekend?

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Yes a Monday or Friday would be perfect if the value was on a weekend

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))

    This will give you the date for 1 month in advance and account for December going into January.


    =IF(WEEKDAY(date value) = 6, 2, IF(WEEKDAY(date value) = 7, 1))

    This will generate a 2 if the date is a Saturday, 1 if the date is a Sunday, and nothing for Monday through Friday.


    If we drop our date value formula into the IF function, we will know whether we need to add 0, 1, or 2 days based on the weekday of the date formula.

    =IF(WEEKDAY(IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))) = 6, 2, IF(WEEKDAY(IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))) = 7, 1))


    Then we add this to the date formula's output to generate the date for 1 month later with the following Monday's date if it happens to fall on a Saturday or Sunday.

    =IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row))) + IF(WEEKDAY(IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))) = 6, 2, IF(WEEKDAY(IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))) = 7, 1))

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul


    thank you for taking the time to look into this, looks amazingly complicated.

    I have added the formula but still return Sunday dates ( Saturdays no longer appearing)

    any help appreciated

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you copy/paste the formula from your sheet to her and maybe provide a screenshot with a few dates that are working properly vs a few dates that are not?

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,


    Bit techophobe with attachment but hopefully you can see the formula used in the cell where it pulls through a Sunday. Basically adding a month to the column date above.

    Regards


    Dave

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul


    That might be better?

    sorry


    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,


    The same formula but in row 2 it would point to Due 1 in all aspects. Row 4 would point at Due 3 e,t,c

    Hope that helps

    Regards

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That's how it SHOULD be. I am just trying to make sure that is how it ACTUALLY is.

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul


    Happy to share the sheet if that helps, but just double checked my self and all the formulas are from the row above. Starting date is Thursday 30th Jan and next month pulls through Sunday 1st March.

    All very weird, but really appreciate your time looking into this

    Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!