Hi,
Excel 2010
I am logging the contents of my Photo negatives,

column subject1, next column date S1, next subject2, next date S2 and so on.
Sometimes I have written on them an exact date, other times a month and year, other times just a year. I have chosen the format for the column that gives 20 March 1987 for example but if the negs are marked Nov 1991 I end up with 01 November 1991., and I wish just to see Nov 1991., as a full date is wrong data.


Furthermore I dont want September but just Sept, as with many columns it making things far too wide.
if I then find the date e.g. 11 Nov 1991 I want that cell to be able to change to that full date.
Many times I have full dates and just M/Y or even Y dates all for the same sheet, so what can I do, ?
I dont need to perform maths on the dates, like establishing the duration between two dates, its just for Photos, maybe to be able to date order the sheet but on the sheet I am currently on the roll number is the deciding factor and thats already in numerical order so no ordering of dates at all.



DBenz

Can you upload a worksheet with some examples so we dont have to try and re-crate? The more the better

Hi, see attached.
note if I type a year I get a strange date, so I have to enter the year in the subject box else I cant log the year at all; and typing a month and year I get 01 of the month., so if I see 01 February I know to ignore the day, though it might in fact be 01 occasionally. 1in 33 or 31 or 28 or 29 chance !


I want Sept not September as well to keep column widths down.



DBenz

Attached Files

Photos subject and Storage Location v2 - forum.xlsx (9.2 KB, 5 views)

so we have 3 senarios

Actual Date
Approx date
Year

As dates are just numbers you could go with a simple fix for the year and actual date

=IF(C2<2500,TEXT(C2,"@"),TEXT(C2,"mmm yyyy"))

If the number in the data column is less than 2500 it must be a year, so just keep as is. Other than that it is a date so we format as MMM YYYY

Working out if the date is approximate or actual date is going to be much harder - you could do an if on the day and say that if the day is 1 then it must be an approx date but this isnt 100%

=IF(C2<2500,TEXT(C2,"@"),IF(DAY(C2)=1,TEXT(C2,"MMM YYYY"),TEXT(C2,"DD mmm yyyy")))


Let me know if its any help

Attached Files

Photos subject and Storage Location v2 - forum_Purfleet.xlsx (10.7 KB, 5 views)

Hi,
I see you have that formula in a new column immediate to right of my date column. if I copy that to my date column I get Sep-71 when I type sept 1972, and I get 15 apr 69 if I type a full date 15 April 1969. the formulA ceases to exist in the cell just typed in,as well it might as I have then superceded it. It needs to pull the data in from the date column.
It is giving me a year 1969 or a month year Sept 1971 or a full date 23 Nov 1991 so thats working.

How can I have this so I havent a third column as its made the data 3 columns wide per subject now., and I have two columns with dates in so a double vision confusion going on.


DBenz

Does that mean it is working? you will need to drag it down to all rows. I have added in another if for blank dates

=IF(C2="","",IF(C2<2500,TEXT(C2,"@"),IF(DAY(C2)=1, TEXT(C2,"MMM YYYY"),TEXT(C2,"DD mmm yyyy"))))

The formula needs data to work from thats why the extra column, the best option in my opinion is to go with the 3 columns then hide your date column - personally i wouldnt go with the multiple columns across the worksheet but that is just because its not a proper excel table/layout but your call.

The other option would be for VBA to do a simular process on each cell in the date range and over write your data with the new calculated data, but i think that is overkill and also you will lose the data you typed if the calculation or formula was ever wrong.

Hiding seems the easier and most efficent step

Attached Files

Photos subject and Storage Location v2 - forum_Purfleet.xlsx (10.9 KB, 5 views)

Hi,

yes its giving me e.g. a MM/YYYY date in column 3 if I enter such a date in column 2, 3 having your formula in., and yes I would drag it down to all rows.
I have as said subject1 date1, subject2 date 2 and so on along a row, one row per roll of film, makes sens to have a row per film as I have 400 or more rolls so downwards is the direction for such !
How would you have designed it ?


If I hide the column then as I need to type the date such as MM/YYYY into column 2 for your formula in column 3 to feed on it and show a MM/YYYY type date, I wont be able to type the date in. It needs to be visible, though one could say after entering subject hit tab which goes to column 2 and type date but I dont always have a date immediate available to enter, so that would mean clicking after the text then hit tab, its doable if I am careful.
Would that work ?
With all this obviously its saying that Excel doesnt have a format that accepts entry of YYYY or MM/YYYY or DD/MM/YYYY and displays as such. I am sure there are many folk frustrated by the fact that one must always know the full date for each entry.


DBenz

I think your layout is better for how you want to see it, but it is not in the style of a proper excel table.


In your example you would need to keep the date you type visible until you are done then hide it, not perfect but how often will you need to hide/show?

Usually data is entered as data, you then you spend time making it look nice - 1 sheet for data entry, 1 sheet for showing

Maybe a couple of more ideas.

Excel Table

On the attached i have added an 'Alternative sheet' - this is how i would add the data. Roll, Subject, Subject date then the 'show date'. This can be filtered to show what is on row 1 for example. But i have also added a pivot table on sheet2 which can be filtered and refreshed as you add data.

Or....

Outline Grouping (which is on the data tab)

On your Print films sheet i have added Grouping on the Subject date column (its simular to hiding but a bit easier to show

You can click the plus at the top of the column to unhide 1 column or the number of the left to show all of them, tab still skips them but it might work better for you.

Personally i would so with the proper excel table layout, but that is just my preference.

Attached Files

Photos subject and Storage Location v2 - forum_Purfleet(1)_v3.xlsx (16.6 KB, 14 views)

What did you decide to do in the end?

Hi Purfleet,

slightly late in getting back to you but ebay kicked in and wiped out my time.

This is MOST KIND of you, forums at their best.
I have looked at the three sheets, I see the sense of having the data not going off screen, different to my 1 roll per row which was the way my brain expects to see a roll shown, and I like sheet 'alternative', I also like Pivot table of that sheet. I have never ever read up on Pivot tables and like to know whats 'under the bonnet' and be able to service it, so feel uneasy about something that is probably very good and best but that I know nothing about should I need to alter or repair it.
I havent time just now to understand pivot tables as I have deadlines to meet.
If I fill in the first sheet can I later on turn it into 'alternative' sheet or is that not wise ?


Saying that my base level thoughts/brain still likes the idea of one row per film, and after 10 mins thought on this still runs to that corner of the room !
Maybe there is a way of retracting and expanding rows so I can show 1 row per film end expand it to show the subject column and its entries for one row, that then suits both camps.
I would need to know how that is done from you if so.

I just need to see at a glance 1 row per film for a quick mental overview.


So unless that is possible, I am going to have to give in to my brain for the moment to go with the sheet with the + that hides or shows the date entry column.


Now as I need more of those + things and to apply this sample sheet method to the workbook sheet with loads of rows already filled in, how did you do that ?


Regards


DBenz

The real benefit of the Alternative & Pivot table is that if anything goes wrong on the pivot table you can delete and start again as the data is on the alternative sheet and in a decent format.

It would be a bit of a chore to change from the across view you start with to the alternative view. Not impossible but manual work.

The outline is easy to add, just select the column you want to be able to hide then click on Data > Group (on the right under Outline)