VBA Holiday Dates by Week End Date

[Finalfight40]

Hi All

I'm not sure if something like this is possible so i thought i'd ask.

Currently we have a system where we store our holiday dates and we need to update another spreadsheet from this but it is not exported in a friendly fashion for this.

Please see below as i have a description of each column and a desired result.

I have 5 columns which i am interested in A to E (the rest will be deleted)

Column A - Persons Name
Column B - Start Date
Column C - End Date
Column D - Vacation
Column E - Note (If this field is populated, then the vacation will be half a day, otherwise it will be a full day)

The data would need to be organised by week end date (the Friday). Below i have used an example based on this week and next week.

How the data is exported:

Assigned To	Start Date	End Date	Vacation	Note
Andy	28/05/2018	28/05/2018	x
Andy	30/05/2018	31/05/2018	x
Bob	29/05/2018	31/05/2018	x
Lucy	28/05/2018	28/05/2018	x	PM ONLY
Peter	31/05/2018	01/06/2018	x
Peter	04/06/2018	05/06/2018	x

<tbody>
</tbody>

Desired Result (on a new sheet):

	01 June 2018	08 June 2018
Andy	Monday, Wednesday, Thursday
Bob	Tuesday, Wednesday, Thursday
Lucy	Monday (Half Day)
Peter	Thursday, Friday	Monday, Tuesday

<tbody>
</tbody>

Would it be possible to get something that could resemble this?

Thank you in advance for taking your time to read this.

 

[oldbrewer]

Assigned To

Start Date

End Date

Vacation

Note

days

start day

end day

Andy

28/05/2018

28/05/2018

x

1

Mon

Mon

Andy

30/05/2018

31/05/2018

x

2

Wed

Thu

Bob

29/05/2018

31/05/2018

x

3

Tue

Thu

Desired Result (on a new sheet):

Lucy

28/05/2018

28/05/2018

x

PM ONLY

0.5

Mon

Mon

Peter

31/05/2018

01/06/2018

x

2

Thu

Fri

01-Jun-18

08-Jun-18

Peter

04/06/2018

05/06/2018

x

2

Mon

Tue

Andy

Monday, Wednesday, Thursday

Bob

Tuesday, Wednesday, Thursday

Lucy

Monday (Half Day)

Peter

Thursday, Friday

Monday, Tuesday

this offset look up table deals only with 5 days max in a session

end day

mon

tue

wed

thu

fri

mon

mon

mon, tue

mon, tue, wed

mon, tue, wed, thu

mon, tue, wed, thu, fri

start day

tue

tue, wed, thu, fri, mon

tue

tue, wed

tue, wed, thu

tue, wed, thu, fri

wed

wed, thu, fri, mon

wed, thu, fri, mon, tue

wed

wed, thu

wed, thu, fri

thu

thu, fri, mon

thu, fri, mon, tue

thu, fri, mon, tue, wed

thu

thu, fri

fri

fri, mon

fri, mon, tue

fri, mon, tue, wed

fri, mon, tue, wed, thu

fri

Andy

mon

Andy

wed, thu

Bob

tue, wed, thu

Lucy

mon

Peter

thu, fri

Peter

mon, tue

Andy

mon

Andy

wed, thu, mon

Bob

tue, wed, thu

Lucy

mon

Peter

thu, fri

Peter

mon, tue, thu, fri

using helpers I got close to what you want…

not sure how to mark the half day

<colgroup><col><col><col><col span="5"><col><col span="9"></colgroup><tbody>
</tbody>

 

[oldbrewer]

Assigned To

Start Date

End Date

Vacation

Note

days

start day

end day

Andy

28/05/2018

28/05/2018

x

1

Mon

Mon

Andy

30/05/2018

31/05/2018

x

2

Wed

Thu

Bob

29/05/2018

31/05/2018

x

3

Tue

Thu

Desired Result (on a new sheet):

Lucy

28/05/2018

28/05/2018

x

PM ONLY

0.5

Mon

Mon

Peter

31/05/2018

01/06/2018

x

2

Thu

Fri

01-Jun-18

08-Jun-18

Peter

04/06/2018

05/06/2018

x

2

Mon

Tue

Andy

Monday, Wednesday, Thursday

Bob

Tuesday, Wednesday, Thursday

Lucy

Monday (Half Day)

Peter

Thursday, Friday

Monday, Tuesday

this offset look up table deals only with 5 days max in a session

end day

mon

tue

wed

thu

fri

mon

mon

mon, tue

mon, tue, wed

mon, tue, wed, thu

mon, tue, wed, thu, fri

start day

tue

tue, wed, thu, fri, mon

tue

tue, wed

tue, wed, thu

tue, wed, thu, fri

wed

wed, thu, fri, mon

wed, thu, fri, mon, tue

wed

wed, thu

wed, thu, fri

thu

thu, fri, mon

thu, fri, mon, tue

thu, fri, mon, tue, wed

thu

thu, fri

fri

fri, mon

fri, mon, tue

fri, mon, tue, wed

fri, mon, tue, wed, thu

fri

Andy

mon

Andy

wed, thu

Bob

tue, wed, thu

Lucy

mon

Peter

thu, fri

Peter

mon, tue

Andy

mon

Andy

mon, wed, thu

Bob

tue, wed, thu

silly error corrected

Lucy

mon

Peter

thu, fri

Peter

thu, fri, mon, tue

using helpers I got close to what you want…

not sure how to mark the half day

<colgroup><col><col><col><col span="5"><col><col span="9"></colgroup><tbody>
</tbody>

 

[Finalfight40]

Hi Brew

Thank you for taking the time to have a look at this problem.

I have no problem using helper columns when it comes to this.

The half day i can live without as they are so rare that they could be typed in manually.

If you could let me know how you were able to get this, i would greatly appreciate it.

 

[oldbrewer]

***'d To

Start

End Date

Vac

Note

days

start day

end day

Andy

28/05/2018

28/05/2018

x

1

Mon

Mon

Andy

30/05/2018

31/05/2018

x

2

Wed

Thu

Bob

29/05/2018

31/05/2018

x

3

Tue

Thu

Lucy

28/05/2018

28/05/2018

x

PM ONLY

0.5

Mon

Mon

Peter

31/05/2018

01/06/2018

x

2

Thu

Fri

Peter

04/06/2018

05/06/2018

x

2

Mon

Tue

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

this offset look up table deals only with 5 days max in a session

end day

row 15

mon

tue

wed

thu

fri

mon

mon

mon, tue

mon, tue, wed

mon, tue, wed, thu

mon, tue, wed, thu, fri

start day

tue

tue, wed, thu, fri, mon

tue

tue, wed

tue, wed, thu

tue, wed, thu, fri

wed

wed, thu, fri, mon

wed, thu, fri, mon, tue

wed

wed, thu

wed, thu, fri

thu

thu, fri, mon

thu, fri, mon, tue

thu, fri, mon, tue, wed

thu

thu, fri

fri

fri, mon

fri, mon, tue

fri, mon, tue, wed

fri, mon, tue, wed, thu

fri

row 26

Andy

mon

Andy

wed, thu

Bob

tue, wed, thu

Lucy

mon

Peter

thu, fri

Peter

mon, tue

Andy

mon

row 35

Andy

mon, wed, thu

Bob

tue, wed, thu

silly error corrected

Lucy

mon

Peter

thu, fri

Peter

thu, fri, mon, tue

the mon in H2 comes from

=TEXT(B2,"ddd")

the mon in I2 comes from

=IF(OR(E2<>"",C2=B2),H2,TEXT(C2,"ddd"))

the mon in H26 comes from

=OFFSET($K$15, MATCH(H2,$K$16:$K$20,0),MATCH(I2,$L$15:$P$15,0))

the mon, wed, thu in H35 comes from

=IF(G35=G34,H26&", "&H27,H27)

may be alter the formula so that for example H5

( the Monday start day for Lucy)

to

=IF(E5<>"",TEXT(B5,"ddd")&" "&E5,TEXT(B5,"ddd"))

<colgroup><col><col><col><col span="2"><col><col><col span="3"><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Finalfight40]

Interesting.

I will test this out soon but from what i can see, i think i can work with this. It will definitely make the long manual job a lot shorter and easier to work with.

Thank you for taking a look and coming back to me, i know it wasn't a quick 1 line answer but i was honestly stumped as to what path i should be looking at.

I appreciate it.

 

[oldbrewer]

I am 72 and it is essential to keep my brain exercised !!! Glad to help - come back if there are issues,.,.,.,.