Date workings

[benoj2005]

I've managed to get the info I need for the "timesheet" worksheet, but the workings behind it on the "Workings" worksheet feel a very inefficient. Is anyone able to streamline it???

Basically want to work out for each week in a given month:

-W/C date (Monday)
-First working day date (W/Ds being Mon-Fri)
-Last working day date
-Number of working days

This data is currently on the workings sheet in the orange/yellow table on the right but to get there I've had to use a sub working table in grey/black that feels very long winded


http://www.filedropper.com/dateworkings (Can you no longer upload files directly to this forum?)

Thanks

[Glenn Kennedy]

Try this out (not extensively tested...).

BtW, you can upload files here still. But. Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[benoj2005]

Hi

Thanks for having a go Glenn.

Column C needs to be "First working day of the week date" and column D needs to be "Last working day of the week date". So in the Mar 18 example, yours returns

Week W/C Calendar Start Calendar End Working Days
1 26/02/2018 01/03/2018 04/03/2018 2
2 05/03/2018 05/03/2018 11/03/2018 5
3 12/03/2018 12/03/2018 18/03/2018 5
4 19/03/2018 19/03/2018 25/03/2018 5
5 26/03/2018 26/03/2018 01/04/2018 4

This should show

Week W/C 1st W.D Last W.D Working Days
1 26/02/2018 01/03/2018 02/03/2018 2
2 05/03/2018 05/03/2018 09/03/2018 5
3 12/03/2018 12/03/2018 16/03/2018 5
4 19/03/2018 19/03/2018 23/03/2018 5
5 26/03/2018 26/03/2018 29/03/2018 4 (Bank holiday on the 30th)

Are you able to help tweak it? Thanks

[davsth]

The tweak is easy, and to learn excel you should really try and work that out yourself.
currently in d5 =IFERROR(B5+6,"") so the end of the week is the start of the week (b5) plus 6 days, you want it to stop on the friday and so plus 4 days.
=IFERROR(B5+4,"")

[benoj2005]

The tweak is easy, and to learn excel you should really try and work that out yourself.
currently in d5 =IFERROR(B5+6,"") so the end of the week is the start of the week (b5) plus 6 days, you want it to stop on the friday and so plus 4 days.
=IFERROR(B5+4,"")

With a response like that I'd rather you had not bothered to be honest. I do know Excel and before I posted I did come up with a solution myself. The request was to HELP streamline my method which felt rather long winded. Glenn has kindly done this but it needs a small tweak that I've had a go at doing but can't come up with a neat quick solution so was seeing if I could get further guidance. You've had a go so thanks for that but it's not actually solving the issue I asked about as you've completely ignored the bank holiday issue and your response also has a sly dig bundled in with it that is unnecessary.

[Glenn Kennedy]

It's not quite that simple, 'cos of the bank holiday. Use this:

=IFERROR(WORKDAY(C5,E5-1,T_BH[Bank holidays]),"")

Improved (correct!!) formula for column E

=IFERROR(NETWORKDAYS(C5,B5+4,T_BH[Bank holidays]),"")

[benoj2005]

It's not quite that simple, 'cos of the bank holiday. Use this:

=IFERROR(WORKDAY(C5,E5-1,T_BH[Bank holidays]),"")

Improved (correct!!) formula for column E

=IFERROR(NETWORKDAYS(C5,B5+4,T_BH[Bank holidays]),"")

Thankyou. Not looking very good for Apr18 where there was a bank holiday on the first Monday. Column C needs a networkdays in it to I feel

[Glenn Kennedy]

Apologies for the delay. I've been out monkeying around. Try this. Tested more than the first ones!!

[benoj2005]

The first week in Apr18 still looks a bit astray

Week Monday First WD of weekLast WD of weekWorking Days
1 26/03/2018 02/04/2018 29/03/2018 -2
2 02/04/2018 02/04/2018 06/04/2018 5
3 09/04/2018 09/04/2018 13/04/2018 5
4 16/04/2018 16/04/2018 20/04/2018 5
5 23/04/2018 23/04/2018 27/04/2018 5
6 30/04/2018 30/04/2018 30/04/2018 1

First working day and last working day should essentially be blank as the 1st Apr is a sunday. Any ideas? Thankyou for your help!

[Glenn Kennedy]

Any ideas, he asks... Yes. Time to take me out into a field and shoot me.

Another go!!

[benoj2005]

Haha sorry this is turning into one of those requests that I bet you never offered to help with!!

Apr18 looks good, it now ignores week 1 being the 1st Apr (sunday), I was hoping it would still show W/C the 26th March and then no working days or dates but that's not a biggie. Problem is is Dec18 is the opposite, week 1 shows the W/C 26th nov but with first and last working days the same as shown on week 2. The 2 non working days (Sat / Sun) seem to be the issue.

[davsth]

I have not checked everything but perhaps b5 is =IF(WEEKDAY(B1,2)>=6,7,0)+$B$1-CHOOSE(WEEKDAY($B$1,2),0,1,2,3,4,5,6)

[benoj2005]

I have not checked everything but perhaps b5 is =IF(WEEKDAY(B1,2)>=6,7,0)+$B$1-CHOOSE(WEEKDAY($B$1,2),0,1,2,3,4,5,6)

Ye I suppose that's one way of doing it, was trying to keep the W/C date even if the month starts with a sat/sun but that works so thankyou for all your help!

[benoj2005]

Sorry to revive this old thread but I've realised after testing this for a while that cell D5 is not working when the first working day of the month falls on a Thursday or Friday. I've done a work around but it feels pretty long winded with lots ifs so was wondering if anyone could come up with anything more efficient. Workbook attached with the formula that dosent work.

Useful months to check the formula in D5 works are:

Oct 18 Mon 1st WD
Jan 19 Tue 1st WD
Aug 18 Wed 1st WD
Nov 18 Thu 1st WD
Mar 19 Fri 1st WD

[davsth]

perhaps
c5 ==IFERROR(IF(MONTH(B1)=MONTH(B5),WORKDAY(MAX($B$1-1,B5-1),1,T_BH[Bank holidays]),$B$1),"")
d5 =IFERROR(WORKDAY(MIN(EOMONTH($B$1,0)+1,B5+5),-1,T_BH[Bank holidays]),"")

[benoj2005]

perhaps
c5 ==IFERROR(IF(MONTH(B1)=MONTH(B5),WORKDAY(MAX($B$1-1,B5-1),1,T_BH[Bank holidays]),$B$1),"")
d5 =IFERROR(WORKDAY(MIN(EOMONTH($B$1,0)+1,B5+5),-1,T_BH[Bank holidays]),"")

Thanks, D5 works great from what I've just tested. I'll leave c5 as it was as it seemed to be working fine and was a shorter formula, unless you spotted it wasn't working??