Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refer to a Single cell in a name range | Excel Worksheet Functions | |||
Named Range reference via single Cell | Excel Discussion (Misc queries) | |||
Range of numbers in a single cell | Excel Discussion (Misc queries) | |||
looking for range of text in a single cell | Excel Discussion (Misc queries) | |||
How to count a range of values in a single cell? | Excel Worksheet Functions |