A way to display a next date based on either weeks or months?

[andyo1]

I have made a start on this.

In cell A8 you enter the date, in C8 you enter the frequency in weeks and in F8 you get the next date. For this I have this formula.
=IF(ISBLANK(A8),"",WORKDAY((A8+C8*7)-1,1,))

Now and again a number per calendar month might have to be entered. Is there a way to have this also in C8? If needed I can make sure only "1M" "2M" "3M" etc is entered.

This is the formula I have started on for the per calendar month, but it would be nice to not have another column just for the occasional per calendar month.

=DATE(YEAR(A8),MONTH(A8)+D8,DAY(A8))

Any help would be great!

Thanks

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT a release number like 2022) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[andyo1]

Thanks. Trying to attach it now, let's see.

Ive not done any excel work for a long time.

Date_V2.xlsx

[tanasedn]

Hi,
You can add a column Interval(column D) next to your Frequency(column C) in witch you can specify the interval(let say W for weeks and M for months). And in F column insert the formula

Please Login or Register  to view this content.

[tanasedn]

I replied before seeing your file. In your case, the formula will be entered on column G if after the Frequency weeks column you will enter the interval

[andyo1]

Thanks this is great!

I tried to get it to work so that the weeks are default so I would only have to add the "M" when needed. Ive not got this working, but I think this should work, Ive added the choice of W or M and both round off to the WORKDAY:

=IF(ISBLANK(A8),"",IF(D8="M",WORKDAY(EDATE(A8,C8)-1,1,),(IF(D8="W",WORKDAY((A8+C8*7)-1,1)))))

Any other improvements?

[andyo1]

Im getting a bit lost with WORKDAY and WORKDAY.INT What actually is "-1,1" saying?

Is there a way I can make this also skip Fridays as well as the weekend. So if a date for some reason fell on a Friday it will push it to Monday.

Is my weekly part of the formula ok or should it be simplified? - (IF(D8="W",WORKDAY((A8+C8*7)-1,1)))))

Thanks again!

[JeteMc]

To skip Fridays, try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[andyo1]

Thank you so much! That is really helpful. Sorry for the delay in responding.

I just typed out a reply and another question but for some reason it didn't send or save, so I will try again now but cut it down slightly.

I am now thinking about displaying ongoing tax quarter totals. So based on the UK format date in column A between 6 April to 5 July for example. I tried to use my total formula =SUM(IF(G:G="N",E:E)) AND a date range, but I was only getting the full total of column E in return.

Will be great to see this working!

[JeteMc]

As the data on the Domestic sheet is a table (Table4) I suggest using structured references instead of column references.
The formula could be:

Please Login or Register  to view this content.

Note that the tax quarter start date is assumed to be in cell C1 and the end date is in cell D1.
Let us know if you have any questions.

[andyo1]

Excellent! You have helped so much and I'm learning too. This is good as the list can go on for over the year, as an overview, and the dates in C1 and D1 can just be changed.

[AliGW]

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

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) 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 all those who offered help.

[JeteMc]

You're Welcome and thank you for the feedback. As AliGW stated, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.