i need to copy the dates (from date to date) if i have specific number in the row

[gomaa1239]

Hi friends
happy to write to you for the first time

could you please help me urgently with the below excel sheet

i have a full 2 month dates in row A and i have a numbers at the rest of rows (b,c,d, etc) and i need to write in column B and c the dates from - to ( 07.04.16 to 11.04.16) if i have number 3 in the other rows

name----from date---- to date-----07.04.16----08.04.16----09.04.16----10.04.16----11.04.16----12.04.16----13.04.16
jac------------------------------------3-----------3-----------3------------3----------2-----------5-----------7
ali-------------------------------------5-----------3-----------3------------3----------2-----------5-----------7
joe------------------------------------3-----------3-----------5------------3----------3-----------3-----------3

results should be

name----from date---- to date-----07.04.16----08.04.16----09.04.16----10.04.16----11.04.16----12.04.16----13.04.16
jac------07.04.16-----10.04.16---------3-----------3-----------3------------3----------2-----------5-----------7
ali-------08.04.16-----10.04.16---------5-----------3-----------3------------3----------2-----------5-----------7
joe-------------------------3-----------3-----------5------------3----------3-----------3-----------3

in the last row i need to have 4 columns (can i do this or not)




thanks in advance for your help

[NeedForExcel]

Not very clear..

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[Rob Kennedy]

I'm working on it, just a simple question.

Do you mean that, in the last row, you want 'from' date and 'to' date to pick up that there are four 3's in a row, and ignore the two 3's in that row that precede it? To return a from date of 10/4/16 and a to date of 13/4/16. On what rules will it ignore the two 3's? Because it must pick up the longest string of 3's?

Thanks.

[Rob Kennedy]

See example file I have attached.

[Rob Kennedy]

See attached
Does this answer your question?

[gomaa1239]

dears,

sorry if my description was not clear,

i attached you the excel sheet and i mention on it an Example for the results i want to show

i just need to look at a column range within a row and return the column header in that row where the first value is bigger than 3 to be copied in "from (1)"
and copy the the column header of last value which is bigger than 3 _then i will have blank cells_ i need to repeat the same thing aging after the blank
cells and so on

look its an hotel allotments sheet and i have a lot of dates and i need to knew what dates (date from to) i have more than 3 booked rooms so i may have 3 rooms
booked from 1/4 till 12/4 then i have some dates where there are just 1 room booked 13/4 till 18/4 then some blank dates where there are no booked rooms

[gomaa1239]

Hi Rob,

Thanks a lot for your help this is what im looking for but i don't knew how to do it if i have a sheet of 100 columns, please check the sheet i attached in my previous reply and try to apply your formulas for this sheet

Thanks a lot for your help

really much appreciated.

[Rob Kennedy]

What is the maximum number of days in a row that you might find a date range that fits? Possibly all 100 columns?

My previous method works on constructing a text string of one digit per day, then searching within that string for the longest list of the
searched number ("3") then returning a value to tell you which dates correspond to the start and end of the string of 3's.

In words, will the results be as follows?
"The start and end dates of every range of dates where there are 3 or more bookings every day continuously." Is this correct?

Is it specifically the number 3 or above that you want to search for? Or might your minimum search criterion change? Might you
want to perform the same search but at a time when you are looking for 4 bookings and greater? etc...

Do you want to return just the next two dates ranges (in your example start (1), stop (1), start (2), stop (2), two ranges), or do you want to return ALL the date ranges?

[Rob Kennedy]

See attached.

[gomaa1239]

Hi Rob,

thanks a lot for your help

i need to search for numbers greater than 3 (i don't need 3 itself) i need to get the results as of 4
yes its correct

Do you want to return just the next two dates ranges (in your example start (1), stop (1), start (2), stop (2), two ranges), or do you want to return ALL the date ranges? i need to get all data range.

thanks again for your help

[gomaa1239]

Hi Rob,

the attached folder is not what im looking for.

Thanks

[Rob Kennedy]

Are you still looking for a solution to this problem?

[gomaa1239]

thanks a lot it has been solved