Find date query and Display issue

V

VBA Noob

Hi,

Two problems/questions.

A user has a spreadsheet with dates and times mixed in Col E e.g 01/0
(07:30-17:30), 02/07 (10:00-18:00). They appear to have the same forma
but assume it may change as a couple of users. It could be for 16 days.

I'm got the first date in Col D but I'm looking to extract the las
date. The above example it would be 02/07. Is the best way find th
last day to use the right function ??

Once I have the start and end date I need to put the info into a table
Along the top Row 1 it will have the dates for July and in Column A i
will have a individual name. The table should then add a one if it's i
the date range. Trying to display visually how many people on each day.
NOTE SOME ENTRY'S WILL SPILL OVER TO AUG

Can anyone point me in the right direction

VBA Noo
 
V

VBA Noob

Hi again,

I've got the forumla so far to rip out the end date element. As
suspected the data is not consist.

=IF(LEN(E20)<=23,TRIM(LEFT(E20,8)),IF(RIGHT(E20,1)<>")",TRIM(MID(E20,LEN(E20)-20,5)),TRIM(MID(E20,LEN(E20)-18,5))))

Still need to deal with the following

1) The word "Agree" instead of Date and Time
2) Start date entered as 2 days e.g 06-07 and not 06/07 time and 07/07
3) End date entered as 5/8 so forumla returns ; 5/8

Hopefully part one is the easy bit

VBA Noo
 
V

VBA Noob

Still working on this one

I'm using the below formula in the table (My Reference in Col A an
Dates in Row 1 e.g 1/7 to 31/7) to count the references that match eac
day

=SUMPRODUCT(--(July06!$D$2:$D$247<=$B$1)*(July06!$Y$2:$Y$247>=$B$1)*(July06!$C$2:$C$247=Sheet2!$A2))

Where B1 = 1/7/06 and so on and A2 to A20 contains my reference.

Questions.

Can I use this to read data from a closed workbook.
How big will it be for 12 months with 20 references.
Is this the neatest solution

VBA Noo
 
Top