Help Requested in the Creation of a Company Calendar

Hi everybody,

With reference to the attached, I am creating a company calendar so as to keep track of pertinent dates. My country of operation is extremely bureaucratic and the government bodies extremely aggressive; a single non-compliance [even an unintentional one] usually results in punitive fines and penalties being levied. It's my opinion that the level of bureaucracy is intentional, to benefit the revenue of the government bodies, but I digress...

To remain compliant and reduce the risk of being fined, it is essential that I have a calendar showing all important dates, which I can add to as and when, but which can update itself year on year. Attached is such a document, or an attempt at one at least.

The 'Master' tab is where the data is input. I input the deliverable in column B, and frequency of the deliverable in column C. Depending on the frequency chosen, depends on which data validation lists in columns D - U become available. Regardless of columns make themselves available, each of the choices in columns D - U allow for the user to create a date using data fro the drop down lists. Once data has been selected from these lists, a date will appear in column W representing the first date in the year that this deliverable is due on (it defaults to the earliest possible date in the year).

The intention is for this information to self-populate into the 'Calendar' tab, based on the date in column W and the frequency selected. For example, row 5 on the 'Master' tab shows management accounts are deliverable monthly, on the 15th of each month. The first date that they are due in the year is the 15 January 2017 (see column W) therefore in my 'Calendar' tab, I want the cell D21 (the narrative box for the day of 15 January 2017) to display the text 'Management Accounts'. The calendar should also detect that management accounts are due monthly therefore the calendar should populate all dates beginning with '15' e.g. 15 Feb, 15 Mar, 15 Apr and so on.

However, reverting back to the 'Master' tab, there exists a second option in column V, which can override the default date in column W. Take row 8 for example - the deliverable is a theoretical new government requirement levied on all companies, which requires all companies to submit a declaration form on Thursday of each week. The frequency selected is therefore 'weekly' and the day of submission is selected as 'Thursday'. The date is column W by default selects the first Thursday of the year, but let's say that this new requirement was only introduced on Thursday 15 June 2017 - I don't want my 'Calendar' tab to show this requirement on every Thursday beginning 05 January 2017, I only want it to show from Thursday 15 June 2017 as it was only a requirement from this date. Hence, I override the date in column W with the relevant date (15/06/2017). The calendar should therefore be based on the 'Final Date' in column Y.

Also, and probably the trickiest part, is that if two or more deliverables are due on the same date, the calendar would need to display all of those deliverables in the same cell in column 'D'. I imagine this will either be nigh on impossible, or easily achieved using a concatenate formula or something similar.

All help and advice welcome. Thanks in advance.

PS there is a function to update the calendar for new years - simply select your chosen year from the drop down list in cell C3 on the 'Calendar' tab.

I have not tried to digest your thread but first impressions is that the attached would provide a simpler approach (courtesy of Pete_UK!)

The number of entries per day can be adjusted with a little "tweaking"

Thanks John. I didn't think to convert the date into numerics and use a concatenate countif function to populate the calendar. Strange, because I use concatente & countif to replicate data elsewhere, quite often. I'll have a go with this on my own spreadsheet as it doesn't look like Pete_UK's sheet has a function for automatically replicating tasks on future dates according to a specified frequency. I'll upload a copy of my calendar for anyones/everyones benefit, if I'm successful in my endeavour.

Just a general observation about the "Base_Data" sheet: there is a lot of (unnecessary in my view) data replication.

Even if Pete's calendar is not suitable, I think you are making something relatively simple into something overly complex!

I'm just about to go out, but I'll take a look at this later on.

Pete

Try

in I8

=IFERROR(INDEX(MASTER!$B$1:$B$37,SUMPRODUCT((MASTER!$AA$1:$OB$37=Calendar!$G8)*ROW(MASTER!$AA$1:$OB$37))),"")

Hi John,

This works almost perfectly, bar one small glitch. In cells I11 and I12 of the 'Calendar' tab, which shouldn't generate any results as there is no corresponding match in the range, we are being given the text 'Deliverable' and 'Management Accounts' respectively.

This error does not repeat anywhere else as far as I can see, it only occurs in the first day of the calendar. I've had a look but I'm totally clueless as to why this is happening. Is there a fix for this?

Thanks for taking the time to provide a solution and FYI I agree there is too much data on the 'Master' tab, but the luxury of taking the time to make my documents as efficient as possible is something I don't have at present, so as long as it works, I'm happy.

Scratch that. I found another problem. Allow me to upload my amended document in a minute.

Try

=IF(SUMPRODUCT((MASTER!$AA$1:$OB$37=Calendar!$G8)*ROW(MASTER!$AA$1:$OB$37))=0,"",INDEX(MASTER!$B$1:$B$37,SUMPRODUCT((MASTER!$AA$1:$OB$37=Calendar!$G8)*ROW(MASTER!$AA$1:$OB$37))))

It seems (using your first formula) there was another error arising on the second day of the month too if you were to increase the number of entries you wanted a day however, your new formula overcomes both these errors and seems to be working perfectly. Thank you.

Now, onto the task of populating the range on the 'Master' tab so that concatenate results appear in the relevant dates, using the final date as a base and the frequency as the interger (right term?). Glenn had assisted me in something similar not too long ago, but I have added a few more frequency values since then. I'm going to have a crack at it myself but no doubt I'll be back on here real soon asking for assistance!

You already have a formula in the cells AC21:OD46, although this is only really taking account of a frequency of "Daily". Are you saying, then, that if C21:C46 contains other frequencies like Weekly, Monthly etc. this formula won't cope, so that's why you want it amended?

It strikes me that if that is the case then it will be quite a long formula, with an IF for each of the 10 frequency types that you have defined in B3:B12, and you will want to copy this to 365 columns over 25 rows, so your file will be very large (it's already 1.5 mB). As John said earlier, this has developed into "something overly complex", though I appreciate the amount of work that you have put in to develop it so far.

I'll have a think of ways to simplify what could be a monster formula.

Hope this helps.

Pete

Questions: Do (we!) only consider workdays?

When you say same date every 2/6/12/24 months/ what happens if the date is a non-workday?

Okay, put these numbers in cells D3:D12:

1
7
14
30
14
91
61
182
365
730

(They are approximations, but let's go with these for the moment to see how it works out).

Then you can put this formula in AC21:

=IF($AA21>AC$20,"",IF($AA21=AC$20,AC$20&"_"&(COUNTA(AC$19:AC20)-COUNTIF(AC$19:AC20,"")),IF(MOD(AC$20-$AA21,VLOOKUP($C21,$B$3:$D$12,3,0))=0,AC$20&"_"&(COUNTA(AC$19:AC20)-COUNTIF(AC$19:AC20,"")),"")))

and copy across and down as required.

I've not had chance to read the later posts while I've been working on this, and I'm going out now (again !!), but I'll pick up on any comments when I get back.

Hope this helps.

Pete

For my intellectual "curiosity" I produced the following set of formulae:

The first 3 use Pete's MOD function but the others (hopefully) meet the more exacting requirements of start dates.

I am sure there are "shorter" versions ....

Hi Stuart,

Sadly, it wasn't a pub I was visiting tonight - just family.

A few general points about this project:

I found it very irritating that it took several seconds to recalculate everything when I was working on the file earlier, and not all the formulae are in place yet - I'm sure the users of the file will also object to its sluggishness, so you need to consider ways that you might improve on this.

Given that the file is still in development mode, it is pointless to extend the formulae in the Calendar sheet, for example, down to row 11000, or to have a full year's worth of formulae in the Master sheet, so if you just had a capability to display, say, two months of data, then you can delete all those extra rows and columns and the file will be much smaller (easier to post here), as well as being quicker. You can always copy formulae down or across when you are ready to compile the release version.

It is also unnecessary during testing to hide sheets - I had to unhide the Base_Data sheet in order to fully understand how the date in column Y of the Master sheet was derived.

You mentioned that it will be easy enough for a user to change the year (it would be easier still for them if it were a drop-down), but you could also introduce a drop-down to select a particular month, and in that case you wouldn't need so many rows in the Calendar sheet or columns in the Master sheet, thus making the file smaller still and quicker to recalculate. On one of my Calendar files the OP needed to be able to display up to 100 items per day, so I set up the capability to display 10 events per day and provided another drop-down to enable a choice of ranges 1-10, 11-20, 21-30 etc. - you might want to think about this approach as another way to make the file smaller.

Hope you find these comments useful.

Pete

@Pete,

I "offered" Stuart one of your calendar workbooks early in the thread but he felt he had put much effort into his version so was reluctant (understandably) to change course.

It might still turn out to (have been) the better option.

Hi Pete, John,

In response to John's post first, the formula work exactly how I want them. Each frequency has it's own separate formula on each row, each of which works perfectly in it's own right. However, when I try to nest them all together in one cell (this is what I will need to do eventually, so I was first trying to nest them on the sheet attached and make sure the nesting works, at which point I would then carry over to my sheet and make the necessary adjustments again), I get 'FALSE' results at the point of incorporating the fortnightly formula i.e. I nested the weekly into the daily to create a daily/weekly formula and it worked fine. When I nest the fortnightly into the daily/weekly I get a FALSE value on the 'fortnightly' row. I removed all '$' in cell references where necessary to allow drag down/across but still the FALSE value remained. I then attempted to nest monthly into the daily/weekly in the hope that it was just the fortnightly formula in isolation that was creating the problem, but the same FALSE value appears. I have been through the formula meticulously and can't find anything obviously wrong, so I'm assuming that there must be some function of the MOD formula that is incompatible with what I'm trying to do (I've never used MOD before so this is purely a guess).

Thanks for your comments Pete. The slow speed of the file is indeed an irritation, but I think once all formula are in place then it will be a case of the user inputting very small amounts of data at a time and it updating [relatively] quickly. You are correct, the speed is something that needs to be improved upon, but the main purpose of creating this file is to create reminders for important deadlines in order to avoid punitive non-compliance fines from aggressive government bodies. The scale of aggressiveness shown by government inspectors in the country I currently work in is astounding and even the smallest non-compliance can result in a hefty fine. For example, when a new employee is recruited, a copy of their signed contract must be stamped in a particular order by five different authorities, with each stamp being affixed within a certain time frame (it doesn't help that the time frames vary with each body). During a compliance audit last year, it was found that we had one single stamp missing from only one employee contract, and consequently we were given a fine running into the tens of thousands of dollars (we managed to successfully refute but regardless, this serves as an example of the business environment I'm operating in). Hence, the sluggishness I doubt will be a major concern of the other management. If the sheet can assist them with deadlines reminders and thus avoid these types of problems, I think the speed [or lack of] can be tolerated until such a time as I can create a more efficient spreadsheet.

Noted your comments on extending the rows unnecessarily and hiding sheets. The rows had been set to 11,000 accommodate an increase in daily activities. Essentially, row 47 on the 'Master' tab counts the number of deliverables per day. Cell AB47 finds the MAX of these. The value in cell AB47 then feeds through to cell F5 on the 'Calendar' tab and the calendar will then allow for this number of entries per day. I assumed a maximum of 30 deliverables on any one day hence 11,000 rows were needed (30 x 365 days).

As for the user being able to change the year from a drop down list, this is already the case (see F3 on the 'Calendar' tab). I will look at introducing a drop down for a particular month to reduce file size.

All comments are helpful, it's always great to receive advice from experts such as yourself and John.

Stuart,
Did you get a chance to look at the "prototype" frequency calculator? If so, does the logic meet you requirement (as all conditions cannot unfortunately be met with simple MOD formulae) ?

The COUNTIFs can be replaced by COUNTA as the tests needs to "non-specific" in that the entries in your table are all different.

John

Hi John,

Sorry, I'm a little confused. By 'prototype frequency calculator' are you referring to the last spreadsheet you uploaded? If so, yes, the logic meets my exact requirements and I have not found any issues during beta testing.

Yes ... that's what I meant. Having looked again at the logic, it does meet the the Quarterly requirement of "Month End" dates AND COUNTA won't work either so it is back to the drawing board for me!

Hi John,

I don't know if the attached is of any benefit to you. This part of a cash flow spreadsheet I use for company. Glenn (a user on this forum) helped construct the formula which performs a similar function, albeit with less frequency types. I don't know if there is anything of any use in here for you.

Please only look at the formula in R12:X13. All cells thereafter may contain some variations (especially the first days of each week post week 1). This may be utterly useless, but on the off chance that it contains anything useful, I thought it best to upload.

Originally Posted by STUARTXL

... Whichever part of your formula generates the result for the 'One-off' frequency, is correctly generating the result in the appropriate date, but all dates thereafter are generating a #N/A error (see attached doc) ...

This is because One-off was not included in the lookup table. Insert a new row 12 on the Master sheet, so the formulae will automatically adjust, copy B13:D13 into B12:D12, then put "One_Off" in B13, "Single entry" in C13, and a large number in D13 (e.g. 10,000). Then the #N/A errors will disappear from cell HQ31 onwards.

Hope this helps.

Pete

Hi Stuart,

I've just noticed that you have a complex formula to generate the dates across the Master sheet - all you need is this in AD21 (might be AD20 for you if you have not added One_Off to the above table):

=AC21+1

then copy across.

Hope this helps.

Pete

Stuart,
Updated my "frequency" file. I have not attempted to merge into a single IF ( word blind !). The Text ("Daily","Weekly" could be replaced by numbers by adding a column to the lookup table.

I replaced the "Y" with the date (knowing it needs to add the COUNTIF concatenation in your real file.

And yes, the file from Glenn helped to clear the fog!

Hi John & Stuart,

The file in Post #28 is great. I've been working on it further, looking at ways of reducing the number of different types of calculation. To me, the Daily, Weekly, Fortnightly and One_Off are all variations of the same thing, with a different increment of days, so they can all be done with one formula.

Similarly, Monthly and Quarterly can use one formula, with a different increment of months.

Bimonthly_two and Biannual_two are again just slightly different versions of the same formula, i.e. Month +2 and Month +6, so they can share the same formula.

The same applies to Annually and Biannual_Two, where the formula is based on Year +1 and Year +2.

Bimonthly_twice needs its own formula.

So this means that there only needs to be 5 different IFs in the composite formula, which will make it a bit easy to compile.

I'm currently working on setting up those five different formulae and applying them to the range of frequencies in each type, so I'll post that when I get through it.

Pete

Pete,
Thanks for looking at this.

I realised that the several of the formula could use the same base e.g MOD for "Daily","Weekly","Fortnightly" etc.

I was "experimenting", particular with those requiring the "Month-End" condition and the same start for the "Bi-monthly".

I had reached a point where I needed to leave it for a while ( I was "IF"ed out!).... but now I know it is in safe hands!!

Thanks again

John

I agree, John your post is great work, your formula really stand by the requirements set out i the frequency definitions. And now I have another Excel formula to add to my skill set (MOD) so thanks!

I'm also currently working on converging the various frequency formula in post 28, into one single formula. This is proving quite the mind musher, there are so many IF statements floating around that it's hard not to get lost. But thanks to you John, the logic now exists, so it's just a case of me finishing the convergence, so I'm not complaining whatsoever. The hard work has been done by you both. Thanks again.

I did realise where I was going wrong with the generations of the FALSE values though. I'm not used to seeing IF formula being nested in the 'VALUE IF TRUE' section of another IF statement. I usually make use of AND or OR functions and I'm clearly not advanced enough to converge such formula, so I'm in the process of 'dumbing down' each single frequency formula with AND or IF functions before I then converge one at a time. It makes it easier for me.

Thanks again both of you.

This is proving quite the mind musher ......

... tell me about it!!!

You will have seen Pete is also working on combining the formulae so I will leave it to you both to complete the finished article.

Look forward to seeing the results.

Hi John,

Sorry to bother you again. But to aid my convergance, could you explain what this formula is doing:

EDATE($A$8,COUNTIF($E$8:E8,">" &0)*2)

Obviously you're attempting to return a date later than the one shown in cell A8, by taking the date in cell A8 and adding on a specified number of months, but I'm not sure what the red highlight part is doing.

In the formula that I'm working on I've actually changed that part to:

EDATE($A22,COUNTIF($F22:F22,"?*")*VLOOKUP($B22,$B$2:$E$12,4,0))

as I'm currently returning a string instead of the date. Basically, John's formula was counting how many entries there were above zero in the range $E$8:E8, but that range will increase as the formula is copied across so it is counting the number of non-zero (or non-blank) cells up to the current cell on that row. That is then multiplied by 2 (as this is for every 2 months). What I have done is to change that fixed value of 2 to a variable which is obtained from the lookup table (i.e. 2 for every 2 months, 6 for every 6 months, and so on), so that the same formula can be re-used for other frequency types. I'll post where I'm up to soon.

Pete

Okay, this is where I'm up to at the moment.

I've taken John's file and tried to identify generic similarities for the different frequency types. I have this formula in cell F16:

=IF($A16>F$15,"",IF(COUNTIF($B$2:$B$5,$B16),IF(MOD(F$15-$A16,VLOOKUP($B16,$B$2:$D$12,3,0))=0,"xxx",""),""))

which can be used for 4 of the types - it has been copied down to F19, then across. This formula is in F22:

=IF($A22>F$15,"",IF(COUNTIF($B$8:$B$11,$B22),IF(OR(F$15=$A22,F$15=EDATE($A22,COUNTIF($E22:E22,"?*")*VLOOKUP($B22,$B$2:$D$12,3,0))),"zzz",""),""))

and again this covers 4 other types, so it has been copied down to F25, then across. I've used this one in F26:

=IF($A26>F$15,"",IF($B26="Bimonthly_Twice",IF(OR(F$15=$A26,DAY(F$15)=DAY($A26),DAY(F$15)=DAY($A26)+14),"aaa","")))

I'm currently trying to come up with a generic formula which will go in F20 and F21 (the ones in the file are what John produced, modified slightly).

The formulae all have the basic form:

=IF(start_date>date_in_row_15,"",frequency_formula)

and the frequency_formula will have the generic form:

IF(exists_in_block_n_of_lookup_table,type_n_formula,"")

except for the Bimonthly_twice formula. In nesting them together, it will be quite straightforward as the "" shown in red will be substituted by the next generic formula, so the overall formula will be along the lines of:

=IF(start_date>date_in_row_15,"",IF(block1,type1_formula,IF(block2,type2_formula,IF(block3,type3_formula,Bimonthly_twice_formula))))

I've noticed that the Bimonthly_twice formula has to have a start date on the 14th of the month or earlier - see what happens if you put 15/01/2017 in cell A26. This might not be a big problem, but if the first time you have to produce this is on 21st of a month, then it should really start from there and then the next ones should be on 7th and 21st of the next month.

Anyway, see what you think of the attached.

I'm posting this now because I'm off on holiday for a week tomorrow, so I won't be able to work on this after tonight (but I'll carry on for now).

Hope this helps.

Pete

I thought as much, but I was doubting whether I was right because of the space between the ">" and the '0'. Shouldn't they be together? I didn't know if the space performed an extra function?

With regards to the formula for the 'Annually' frequency, one of the OR criteria for generating a result is (cell F10):

EDATE($A$10,12)=F$1)

Am I right in thinking that this will only work for one year after that start date? It seems to say IF the date in F$1 is equal to the date in $A$10 plus 12 months, then generate a result. This is fine for the year after the date in $A$10 but what if we roll the calendar forward 2, 3 or 4 years from that date? It doesn't seem to generate a result in this scenario. I have tried using a MOD formula to overcome this problem but keep getting greeted with an error. The only other way I can think of resolving this is to extend the formula, like below:

OR(EDATE($A$10,12)=F$1),EDATE($A$10,24)=F$1),EDATE($A$10,36)=F$1),EDATE($A$10,48)=F$1)

It isn't insurmountable but the converged formula is already quite large and I was wondering if there was a way to avoid this unnecessary extension.

Nearly there with the combined formula so hopefully should have a final product to upload for both your viewing tonight!

I only hope this is "maintenance" free: I would hate to come back in 6 months and decipher the formula in "MASTER"!!!!

It was designed to be maintenance free so in all likelihood, it's not haha! But in all seriousness the only issue I foresee is the limitation in the 'Annually' and 'Bi-annually_Two' strands of the formula, which are limited to working for 60 months and 48 months from the date in column AA respectively).
Other than this, I think it is capable of working successfully on it's own.

I noticed one other problem but it's not for tonight; on the calendar the user has the option to mark a task/deliverable as completed by placing a 'Y' in column J (should be drop down but this can easily be edited). The impact of marking a task as completed is that it no longer appears on the 'Reminders' list (you don't want to see a reminder for a task already completed).
As the 'completed' status are static i.e. remain on the same line within the calendar, this feature works, on the premise that the deliverables also never change lines. But the deliverables can change lines quite easily if the number in cell F5 of the 'Calendar' tab changes. FYI the number in F5 pulls through from AB47 of the 'Master' tab and represents the largest number of deliverables due on any one day in the year.
As the deliverables can change lines but the 'Completed' status remain static, deliverables paired with a completed status can easily grow out of sync. I need to find a way of keeping them synced.

I guess one easy way to overcome this is to set the number in cell F5 as a large number e.g. 30 as it is highly unlikely any one day will ever have 30+ deliverables falling due on it hence the number of lines per day will never increase. I think I'll do this actually....

Originally Posted by STUARTXL

... I need to find a way of keeping them synced ...

The problem is that you are using what is essentially a (variable) report from your data (i.e. output) as a data entry form, but the two things are not linked. You can have a fixed number of lines per day, as you suggest, but the days themselves are variable as you can change the year, and in that case you would want the data input to be wiped. Excel in this case is not working as a database would, where you would allocate that input to a record and (separately) display the record (or fields of it).

I'm glad you were able to compile the composite frequency formula - mine would be a bit shorter as I had 2 generic formulae and 3 individual ones, instead of 12 individual ones, but both end up as "monsters" and will be difficult to maintain in future. I'll test yours out a bit later on if I get chance.

Pete

Deleted - I thought the previous post had got lost (just went to a new page)

Pete