Date display formula which skips weekends

Hey all,

interesting - it does seem like the same data was a bit off in spots. Sorry about that!

I'm a tad conflicted - I don't want people to feel that their work is unappreciated but I can only go with one approach (correct?)... how to use both of your approaches?

@Dave - thanks for the help - it seems to work well. I suppose setting the 'end date' is meaningless, then? Make sense.
This bit you didn't understand is that i need a line where E38 = 04 and F38 = 30. E39 must then = 05 and F39 must = (the Friday, which in this case is = 03). After that the 'routine code' should kick in again.
@mehmetcik - thanks for your great work. I am sadly a complete noob with macros - I'm not even sure how to initialize them / where to past your code. It's late and I'm going to crash but I'm give it a shot in the morning (unless you can upload the file - saving me time but i suppose resulting in me learning less...)

Thanks to the both of you for your solutions!

Haha - amazing!

When i click the comically large button it throws an error =\
runtime error '13'
type mismatch

Any idea what could be going wrong? =(

runtime.jpg

Not sure - see attachment =\

What could I be doing wrong? I open it, allow macros, and press the button.

Do i need to apply the formula in cell I5 anywhere?

Thanks for everyone's help - I wish I could be more useful in this debugging iteration =\

I'm now getting a runtime error 91 =\

Object variable or With block variable not set

and it points to this line:
Z = Columns(3).Find(5, LookIn:=xlValues, Lookat:=xlPart).Row

Don't know if it helps but I'm running excel Pro Plus 2010 (old, i know... is this the issue?)

So when I opened your most recently uploaded file, and replaced the VB code with the code you most recently pasted, I see a whole series of #values all the way across the tables. Maybe I've done something wrong though(?)

Should I throw this into a Google Doc so that we're all working off the same backend?

Hi all,

So is that the solution, then? Just changing the format? I tried it and it didnt work for me =\

Hi all,

I let this go because the use case was still far in the distance but now it's here...

Would one of you lovely people be able to help me get this sorted? We're almost there, just getting some errors (the same as in post 19).

Thanks so much!

Cheers,



Jay

Lol - exceptional patience.

I'm still shocked that this must use VBA... i mean the date skipping should just use a variant of =CEILING(C3 + 1,14) +2 if C3 is 1/4/2019, no??

I guess i'll wait for the experts. lol.

Cheers,


Jay

I'm new to this thread, but I downloaded your file from Post #1 - is that still valid, given that the start date is not a Monday?

This formula (which could be in C2 but for now I've put it in K2) will give you the date for the next Monday after that starting date in I2 (although that is not formatted as a proper date):

=DATE(RIGHT(I2,4),LEFT(I2,2),MID(I2,4,2))-WEEKDAY(DATE(RIGHT(I2,4),LEFT(I2,2),MID(I2,4,2)),2)+8

If you want the previous Monday, just change the 8 to 1 at the end of the formula, but it would be better if you ensured that your start date was a Monday. This calculated date could be formatted to show only the month, and in the adjacent cell it could be formatted to show only the day, and then it is just a matter of adding 4 to the previous date to get a Friday, and adding 7 to the previous date to get the following Monday.

Are you looking for a VBA solution, or do you want me to take this further and look at the 30th April caveat?

Hope this helps.

Pete

Hi Dave,

Okay, maybe I'll reset thing here and re-explain. VBA is not my pref and I don't know how to use it - it was just the avenue put forth by one of the individuals and so we went there.

I have attached a new spreadsheet which is the actual use case. I apologize for the format but i had to chop out the personally identifiable info for obvious reasons. Essentially I benefit from a subsidy for people with major disabilities working in Quebec. The way that this form is constructed is paper based (because, you know, we all love 1990's bureaucracy...). I scanned it, chopped it up into images, and made an excel version where i could enter the data digitally because it's just more efficient (and my handwriting sucks..)

What I'm looking to avoid is having to manually change all of the date ranges in each tab every 8 weeks when i have to submit this thing. Here's the structure for each tab:
For rows 21-28
Column C shows are the "week numbers"
Column D & E show that start month, and day, for that week
Column F & G show that end month, and day, for that same week

Every year this employment contract is renewed and the start / end dates of the contract shift by day or so. As a result i may have to manually change (for ex) E21 from '19' to '20' - and then manually create that cascade through the whole thing. I just want to automate that, so that i change the start date and it auto-adjusts all the way along. I figure that should be simple using the ceiling function but i got tripped up, which is where you guys came to the rescue

The other caveat is more complicated so I'll leave it for now until we get that working - I think it's more trouble than it's worth to automate =\

I hope this helps - I didn't feel comfortable giving this level of detail before but whatever, as long as it gets things done i guess =\

Thanks again for your help,



Jay

Do you want us to work on your latest file, or the earlier one? It strikes me that it might be better to have a separate sheet to do the calculations (along the lines of your first file), and then the sheets for the weeks can just lookup the information in that composite date table.

Did you see the questions that I asked in my previous post? I submitted it just before you, so you were probably busy composing your reply to Dave.

When you submit the form to the Government Agency, do you print it out and post it, or do you send it on-line (maybe as a PDF file)?

Pete

Now it seems that the reverse has happened to our posts. I'll grab a coffee, so that maybe we can get back in synch.

Pete

Just to add, re the caveat:

The one caveat is that I need to make a submission (wherever it falls in the 8 week cycle) to capture all days worked from the previous submission to March 31. So for ex if my last submission was for an 8-week period ending March 1, I would have to submit a half-filled form from March 4 - March 29. It just so happened that last year, given my start date, March 31 was at the end of the 8th week... but that's not always the case.

That said, i can just manually segment off and adjust for the submission of that one form... There's no need to make you guys craft up some bespoke approach just to save me 45 seconds per year... which is why I took it out of my last post.

That said, if there's a simple way about it then awesome.

Also

The option exists to have all of this on one sheet and just have me hide the rows that don't apply when I'm printing the form off, instead of splitting it across multiple tabs. Does that make your lives easier, somehow?

Thanks again!


Jay

In your first post you talked about 30th April being the special case, and now it seems that 31st March is to be considered as special - have the rules changed from when you posted a year ago?

I worked on your first file as it was easier, although most of the cells in the table were formatted as text so that was a bit awkward. I've moved your start date up to row 1 and I don't need the end date. I've also added a message to cell K1 which will pop up if the start date is not a Monday.

It's a lot easier to work with actual dates, so I put this formula in cell C2:

=DATE(RIGHT(I1,4),LEFT(I1,2),MID(I1,4,2))-WEEKDAY(DATE(RIGHT(I1,4),LEFT(I1,2),MID(I1,4,2)),2)+1

to ensure that it is a Monday, and this in D2:

=C2

so that has the same date value. Then these formulae:

C3: =C2+7
D3: =D2+7

just add one week onto the dates. These 2 formulae can be copied down to the bottom of your table, but column C uses a Custom Format of mm and column D a Custom Format of dd. The same custom formatting is applied to columns E and F, which use these formulae:

E2: =C2+4
F2: =D2+4

which are copied down as required.

I haven't tried to implement your special situation yet, but I'm not very happy about a manual adjustment because this might affect the following formulae. Hopefully, though, this gets us someway there.

Hope this helps (for now).

Pete

Another point, relating to the "special case" - is your week taken as being from a Monday to a Sunday, or from Sunday to Saturday, or something else?

If March 31st occurs on a Saturday or Sunday, would that be considered as the End date for the previous Friday, with 1st April taken as the start date in the following week (even if that falls on Sunday)?

And if March 31st falls on a Friday, should the next week begin on 1st April, even though that is a Saturday?

If March 31st falls on a Monday, do we show both Start and End dates as 03 and 31 ?

Pete

Hi Pete,

So I have just called to confirm and the fiscal year end of the employment agency is March 31 (So I'm not sure why I had indicated April 30th before... odd). So that answers that =)

Specific to your last question, each row is only supposed to show from Monday to Friday, so the end date would be the previous Friday. The logic is that one must submit a reimbursement claim for all unclaimed days worked (mon - fri) prior to March 31. So if March 31 happens on a weekend then technically nothing 'changes'. If March 31 is on a Thursday then that row (say 'row 34') counts Mon-Thurs and the next row (Row '35')counts only the Friday.

Also, i am going to use your 'long table' method instead of splitting it into 8-weeks per tab and so I'll just have to change the formula in L57 to sum only what's visible, and then VOILA it will work well!

Does that provide the info required to code in the exception?

Thanks so much!


Jay

Yes, but I'm about to log off for a break, so I'll pick this up later on.

Pete

Perfect - thanks so much!

Hey Pete - hope you had a great weekend =) Just bumping this on the off chance that you had more to add

Cheers,



Jay

I'm sorry, I forgot all about this. I did work on it a bit more that night, but I don't think it was completely finished, so I'll have to look that up again.

Unfortunately I have to take my wife to hospital shortly, so it will probably be this evening before I can pick it up again.

Pete

Actually, I just had a quick look at the file, and I'm not sure if it is finished or not - perhaps you can try it out by putting different starting dates in over 7or 8 years to see if it produces the correct sequences.

You will notice that I used another cell to record the special date - I assumed this to be in the next year.

Hope this helps.

Pete

Thanks for asking, but it was just for tests and scans on her eye, but I knew that we would be hanging around for ages between the various appointments.

It's late now, but I'll take a look at it again tomorrow. I had a feeling in my mind that it wasn't quite finished when I tested it out last time, but I couldn't remember what was wrong so I figured you could do that while I was out today.

Pete

Glad to hear that all is well and that it was nothing too serious.

My post above outlines the only real issue that I can see for now, so if you're able to solve that then I think we're golden =)

Thanks so much!


Jay

There are actually other starting dates that give rise to wrong sequences after 31st March. For instance, with a starting date of 16th August 2021, row 34 terminates with 31st Aug, which is a Thursday. The next row starts with 1st April (Friday), but as I understand it that row should also terminate with 1st April and the next row should start with 4th April, but instead it goes out of synch and alternates from Friday to Tuesday from that point onwards. The fault is with the formula for Friday, so I'll look at that in more detail tomorrow (I've been out tonight, so not had a lot of time to look at it).

Pete

Hi Pete,

Your explanation of the expected use case and the error is spot on.

Just to add a potential edge case for thought - there will be cases where the 31st will actually be on a Friday (like last year)... in which case no actual 'handling' is required and the next row just starts on the Monday. I assume that just means there needs to be an 'if' statement in there somewhere.

Take your time and thanks for helping me with this.

Cheers,


Jay

Hi Jay,

I managed to find a bit of time today to look at this again, and have made the necessary corrections to the file.

In order to make it easier to read, I've introduced two other columns (L and M) with the same formulae as columns C and E, but showing the day of week and the full date. I've also tested it out with a range of starting dates over 8 years, and I show when the "Special Date" occurs for those starting dates, in columns O and P. You can delete these extra columns if you wish, but they do help to show the results more clearly.

Hope this (finally) helps.

Pete

Pete, you're absolutely amazing. Thank you SO much - I am going to integrate this into my context and I'll report back on whether there are any issues!

Glad to help, Jay.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hi Pete - the past few days have been crazy - I'll test it fully tomorrow and the mark as 'solved' =)

Hi Pete,

So I finally had the time to test it fully and it works amazingly! I was wondering if you could make one fast adjustment so that it will fully integrate into the use case seamlessly?

In column G of your sheet can you add "# of hours", and then have the G cells calculate the # of days in that row, and then multiple by 7? Maybe to make this robust for other users who might find the thread we can add "Hours in a work day" to H3 and "7" in I3 so it can be easier to modify?

Basically for each row i need to have a cell showing the # of hours worked for that 'week' - which is 35 always until we hit the special case. This avoids me forgetting to manually change this and having the submission rejected =\

Thanks so much, for everything!

Cheers,


Jay

Hello again, Jay.

I've made the changes that you asked for in the attached file.

Hope this helps.

Pete

Hey Pete!

Thanks that worked great! I'm happy to close this now =) Thank you so much for everything!

Sincerely,


Jay

You're very welcome, Jay, and thanks for the extra rep.

I hope all goes well with integrating it into your main file.

Pete