Date Range in single Cell
 

I have a spreadsheet with date ranges within a single cell is there a
way to search for a date within that range?

i.e.

seth Jan 1-16
fred Jan 17-31
steve Feb 1-15
tom Feb 16-28

Can I find who worked Jan 12. Running a search?

Can anyone help me.

Date Range in single Cell
 

This works to a certain extent. As long as the "dates" don't extend beyond
the current year. For example:

tom Dec 15-Jan 15

That would cause a problem. It could be "dealt" with but the formula is
already a "mess".

D2 = date to look for (entered as a true Excel DATE) = 1/12/2007

Try this formula entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=INDEX(A2:A5,MATCH(1,(DATEVALUE(LEFT(B2:B5,FIND("-",B2:B5)-1))<=D2)*(DATEVALUE(IF(LEN(MID(B2:B5,FIND("-",B2:B5)+1,20))2,MID(B2:B5,FIND("-",B2:B5)+1,20),LEFT(B2:B5,3)&MID(B2:B5,FIND("-",B2:B5)+1,20)))=D2),0))

If you were to use 2 cells for the date range this would soooooooooo much
easier and year crossovers would not be an issue!

Biff

"Unknowledgable Geek" wrote in message
oups.com...
I have a spreadsheet with date ranges within a single cell is there a
way to search for a date within that range?

i.e.

seth Jan 1-16
fred Jan 17-31
steve Feb 1-15
tom Feb 16-28

Can I find who worked Jan 12. Running a search?

Can anyone help me.

Date Range in single Cell
 

I've assumed that you have names in column A starting in cell A1, and
that the date ranges are in B1:B4. I've also assumed that your months
are always abbreviated to 3 letters, and that you have a single space
between the month and the first number, as shown in your example.

I've also noted that your dates do not overlap and that there are no
gaps, so this formula in C1 will give you the start date for each
person's working period:

=DATEVALUE(MID(B1,5,SEARCH("-",B1)-5)&" "&LEFT(B1,3)&" 2007")

Format this cell as a date, and copy this formula down into C2:C4.
I've assumed that you enter the date you are interested in (i.e. 12th
January) into cell F1 in Excel date format. (In the UK I can just
enter 12/1 and Excel will assume I am entering a date and will assume
it is for this year if I dont specify one - if your normal date format
is mm/dd/yy then you should be able to enter 1/12 for 12th January).
Enter this formula in G1:

=INDEX(A1:A4,MATCH(F1,C1:C4))

and you will have the name of the person who worked on that date.
Adjust the ranges A1:A4 and C1:C4 to suit your data.

Hope this helps.

Pete


On Apr 3, 8:24 pm, "Unknowledgable Geek"
wrote:
I have a spreadsheet with date ranges within a single cell is there a
way to search for a date within that range?

i.e.

seth Jan 1-16
fred Jan 17-31
steve Feb 1-15
tom Feb 16-28

Can I find who worked Jan 12. Running a search?

Can anyone help me.

Date Range in single Cell
 

Nice one, Pete! I didn't even think of doing it that way.

You could eliminate the helper column and use this array formula:

D2 = lookup date as a true DATE

=INDEX(A2:A5,MATCH(D2,DATEVALUE(LEFT(B2:B5,FIND("-",B2:B5)-1))))

Biff

"Pete_UK" wrote in message
ups.com...
I've assumed that you have names in column A starting in cell A1, and
that the date ranges are in B1:B4. I've also assumed that your months
are always abbreviated to 3 letters, and that you have a single space
between the month and the first number, as shown in your example.

I've also noted that your dates do not overlap and that there are no
gaps, so this formula in C1 will give you the start date for each
person's working period:

=DATEVALUE(MID(B1,5,SEARCH("-",B1)-5)&" "&LEFT(B1,3)&" 2007")

Format this cell as a date, and copy this formula down into C2:C4.
I've assumed that you enter the date you are interested in (i.e. 12th
January) into cell F1 in Excel date format. (In the UK I can just
enter 12/1 and Excel will assume I am entering a date and will assume
it is for this year if I dont specify one - if your normal date format
is mm/dd/yy then you should be able to enter 1/12 for 12th January).
Enter this formula in G1:

=INDEX(A1:A4,MATCH(F1,C1:C4))

and you will have the name of the person who worked on that date.
Adjust the ranges A1:A4 and C1:C4 to suit your data.

Hope this helps.

Pete


On Apr 3, 8:24 pm, "Unknowledgable Geek"
wrote:
I have a spreadsheet with date ranges within a single cell is there a
way to search for a date within that range?

i.e.

seth Jan 1-16
fred Jan 17-31
steve Feb 1-15
tom Feb 16-28

Can I find who worked Jan 12. Running a search?

Can anyone help me.

Date Range in single Cell
 

Hi Biff, thanks for the praise - I didn't like to comment on your
first attempt !! <bg

Pete

On Apr 3, 11:54 pm, "T. Valko" wrote:
Nice one, Pete! I didn't even think of doing it that way.

You could eliminate the helper column and use this array formula:

D2 = lookup date as a true DATE

=INDEX(A2:A5,MATCH(D2,DATEVALUE(LEFT(B2:B5,FIND("-",B2:B5)-1))))

Biff

"Pete_UK" wrote in message

ups.com...



I've assumed that you have names in column A starting in cell A1, and
that the date ranges are in B1:B4. I've also assumed that your months
are always abbreviated to 3 letters, and that you have a single space
between the month and the first number, as shown in your example.

I've also noted that your dates do not overlap and that there are no
gaps, so this formula in C1 will give you the start date for each
person's working period:

=DATEVALUE(MID(B1,5,SEARCH("-",B1)-5)&" "&LEFT(B1,3)&" 2007")

Format this cell as a date, and copy this formula down into C2:C4.
I've assumed that you enter the date you are interested in (i.e. 12th
January) into cell F1 in Excel date format. (In the UK I can just
enter 12/1 and Excel will assume I am entering a date and will assume
it is for this year if I dont specify one - if your normal date format
is mm/dd/yy then you should be able to enter 1/12 for 12th January).
Enter this formula in G1:

=INDEX(A1:A4,MATCH(F1,C1:C4))

and you will have the name of the person who worked on that date.
Adjust the ranges A1:A4 and C1:C4 to suit your data.

Hope this helps.

Pete

On Apr 3, 8:24 pm, "Unknowledgable Geek"
wrote:
I have a spreadsheet with date ranges within a single cell is there a
way to search for a date within that range?

i.e.

seth Jan 1-16
fred Jan 17-31
steve Feb 1-15
tom Feb 16-28

Can I find who worked Jan 12. Running a search?

Can anyone help me.- Hide quoted text -

- Show quoted text -

Date Range in single Cell
 

I didn't notice the dates were in ascending order until I read your reply. I
worked on the assumption that the dates were random.

Biff

"Pete_UK" wrote in message
ups.com...
Hi Biff, thanks for the praise - I didn't like to comment on your
first attempt !! <bg

Pete

On Apr 3, 11:54 pm, "T. Valko" wrote:
Nice one, Pete! I didn't even think of doing it that way.

You could eliminate the helper column and use this array formula:

D2 = lookup date as a true DATE

=INDEX(A2:A5,MATCH(D2,DATEVALUE(LEFT(B2:B5,FIND("-",B2:B5)-1))))

Biff

"Pete_UK" wrote in message

ups.com...



I've assumed that you have names in column A starting in cell A1, and
that the date ranges are in B1:B4. I've also assumed that your months
are always abbreviated to 3 letters, and that you have a single space
between the month and the first number, as shown in your example.

I've also noted that your dates do not overlap and that there are no
gaps, so this formula in C1 will give you the start date for each
person's working period:

=DATEVALUE(MID(B1,5,SEARCH("-",B1)-5)&" "&LEFT(B1,3)&" 2007")

Format this cell as a date, and copy this formula down into C2:C4.
I've assumed that you enter the date you are interested in (i.e. 12th
January) into cell F1 in Excel date format. (In the UK I can just
enter 12/1 and Excel will assume I am entering a date and will assume
it is for this year if I dont specify one - if your normal date format
is mm/dd/yy then you should be able to enter 1/12 for 12th January).
Enter this formula in G1:

=INDEX(A1:A4,MATCH(F1,C1:C4))

and you will have the name of the person who worked on that date.
Adjust the ranges A1:A4 and C1:C4 to suit your data.

Hope this helps.

Pete

On Apr 3, 8:24 pm, "Unknowledgable Geek"
wrote:
I have a spreadsheet with date ranges within a single cell is there a
way to search for a date within that range?

i.e.

seth Jan 1-16
fred Jan 17-31
steve Feb 1-15
tom Feb 16-28

Can I find who worked Jan 12. Running a search?

Can anyone help me.- Hide quoted text -

- Show quoted text -