Way to Combine dates with in cell

[bdouglas1011]

I have a cell that is populated with dates from another sheet and sometimes these dates take up a lot of space is there a way to combine the dates with the range of the days is has.

Ex. 9/01 9/03 9/04 9/05 9/06 9/07 9/08 9/09 9/10 9/11 9/12 9/13 9/14 9/15 9/16 9/17 9/18 9/19 9/20 9/21 9/22 9/23 9/24 9/25 9/26 9/27
Can you make this 9/01 - 9/27 from formatting the cell a certain way or a way to only pick up the min & max day it see's.

[XOR LX]

Hi.

Is the example you gave all contained within a single cell?

Regards

[bdouglas1011]

Yes it is ...I can attach the sheet it comes from if that would help.

[XOR LX]

Try:

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))&" - "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Regards

[AlKey]

Or this

=LEFT(A1,FIND(" ",A1)-1)&" - "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

[bdouglas1011]

Here is the existing formula how would I combine it with it?

=IF(OR(A10=""),"",'Daily Chgs'!BP60)

[bdouglas1011]

I used

IF(OR(A10=""),"",LEFT('Daily Chgs'!BP60,FIND(" ",'Daily Chgs'!BP60)-1)&" - "&TRIM(RIGHT(SUBSTITUTE('Daily Chgs'!BP60," ",REPT(" ",255)),255)))

The return answer is coming out as

9/01 -

I also used your version and entered the dates into cell a1 and the same return answer came through

[AlKey]

Maybe there is a space after 9/27, try to remove it.

I have adjusted your formula to remove spaces:

=IF(A10="","",LEFT('Daily Chgs'!BP60,FIND(" ",'Daily Chgs'!BP60)-1)&" - "&TRIM(RIGHT(SUBSTITUTE(TRIM('Daily Chgs'!BP60)," ",REPT(" ",255)),255)))

[bdouglas1011]

There is no space after 9/27 but there are spaces between all the dates.

[bdouglas1011]

I have attached the sheet I am using If you look at the Daily Chgs Tab and look At each row for example E:3 thru BN:3 you have boxes to check if that service was used on that particular day. We want to have the dates of services used to be added to the Job Ticket Cell I:10 which corresponds with the service being provided during the Job.

The dates are in E:1 thru BN:1

I have tried several ways to accomplish this with not luck -- If you have to many days it wont combine days to help fit into the cell size and this is a invoice so we cant expand to show every day in the cell.

If you have any ideas that would be great.

[AlKey]

There is no space after 9/27 but there are spaces between all the dates.

Please see attached spreadsheet.

[bdouglas1011]

That formula you modified for me worked great thank you ....I may have to make an addition tomorrow ...but here is the question now.

Say If I have dates:
9/01 9/02 9/03 9/04 9/05
then start back on

9/10 9/11 9/12 9/13 9/14 9/15 9/16 9/17 9/18 9/19 9/20 9/21 9/22 9/23 9/24 9/25 9/26 9/27

Can it return 9/01 -9/05 thru 9/10 - 9/27

not sure if this is possible

[bdouglas1011]

I just copied down the formula to other rows and I noticed that some dates now Don't show the beginning part unless it started with 9/01 for tall the cells that start after 9/01 it only shows the last part ( - 9/06) Almost the opposite from before.


here is the Formula in that cell:

=IF(A17="","",LEFT('Daily Chgs'!BP67,FIND(" ",'Daily Chgs'!BP67)-1)&" - "&TRIM(RIGHT(SUBSTITUTE(TRIM('Daily Chgs'!BP67)," ",REPT(" ",255)),255)))

[AlKey]

Sorry, but without seeing the spreadsheet I can't really do much. If a formula is working on one cell and not with the other then the problem maybe with the whatever is in that cell.

[mehmetcik]

Try this

Please Login or Register  to view this content.

[bdouglas1011]

I have attached the worksheet with that formula in it. I have carried it down the column and it works on some but on the ones
that the date does not start on 9/01 it skips the start and just populates the end ( - 9/09) Just like it did earlier on the
front end before I entered the revised formula of the one I posted. Maybe you can see something that would cause it.

[bdouglas1011]

I also entered the Code ...and the problem with it is when there was no service on a particular day and there is a gap with in the dates (By gap I men extra spaces) It returns a #value! until the extra spaces are taken out then it looks good. Not every day has an entry so the only way I knew to get the dates is to combine them together so it will give a space if no service on that day.

[AlKey]

Please see attached file. There were issues due to the merged cells (BP column) which is always a problem. You should avoid them whenever is possible and should NEVER use merged cells if they are to be referenced in formulas. Another adjustment I made is if there is only one date in BP column formula will display just that date.

[bdouglas1011]

That worked great Thanks.

Now some people are asking me if possible to add .... lets say with a certain date range you had 31 days with in that range but there were 5 days with in that range that actually nothing happened.

So in my charges I would have charged for 25 days but the range for the dates would still display say 9/01 - 10/1 the 31 day range from your list.

Is it possible to have it group the date range only for the days actually used and show possibly 9/01 - 9/15 & 9/21 - 10/1