Find date query and Display issue
 

Hi,

Two problems/questions.

A user has a spreadsheet with dates and times mixed in Col E e.g 01/07
(07:30-17:30), 02/07 (10:00-18:00). They appear to have the same format
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 last
date. The above example it would be 02/07. Is the best way find the
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 it
will have a individual name. The table should then add a one if it's in
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 Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=561464

Find date query and Display issue
 

Hi again,

I've got the forumla so far to rip out the end date element. As I
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 Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=561464

Find date query and Display issue
 

Still working on this one

I'm using the below formula in the table (My Reference in Col A and
Dates in Row 1 e.g 1/7 to 31/7) to count the references that match each
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 Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=561464