I'm trying to put cemetery records in a simple, sortable table, names and dates. The problem is that for some entries I have a full date of birth, death, or burial (e.g. 5/12/1892) and for others only a year (e.g. 1892). Is it possible to sort these? All I can get is the just years in order then the full dates at the end. My data look sort of like this:
Smith John 1892 1/1/1940 2/2/1892
Johnson Sarah 3/12/1900 1880 3/10/1900
One thing I don't know is what number format to use (text, long date, short date....) Sometimes the date I type changes after I type it (like 1892 became March 6, 1905) and sometimes it doesn't even though the cell is the same format.
I have a workbook with worksheets that are named in various ways that I need a VBA macro to sort.
Some sheets have names that begin with numbers.
I am able to get the macro to sort them all alphabetically but those with numbers are always put at the start of the work book instead of within the alphabetical format I want to use
I may have sheet names like:
CAT 3CAT 1CAT 2CAT MONKEY DOG 4DOG 3DOG
This is how I want these sheets sorted:
CAT 1CAT 2CAT 3CAT DOG 3DOG 4DOG MONKEY
The sheet names are examples and not actual sheet names.
Below is my code so far
Code: For lCount = 1 To lShtLast For lCount2 = lCount To lShtLast And Not IsNumeric(Mid(UCase(Sheets(lCount).Name), 1, 1)) If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
Eg. John has first aid and expires on 11/15/2009. I'd like the cell that has this date to highlight red on 10/15/2009.
I have messed around with the conditional formatting and know how to format the cells color and font et., but I can not figure out the formula to use to get my results.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
I have a date of hire column in mm/dd/yyyy with 5000 rows. It contains many different formats and I need to sort it by year. Is it possible to sort by year and include all months, i.e. everyone hired in 2008, by month.
It also seems that Excel is not recognizing some of the formats as dates.
i have a register with items for calibration. I have a column with a date the item is next due cal which is 3 years from date of receipt manually input by myself. say (H64) due 20/11/2010, i would like the cell to be green if todays date is below the date due, but amber if todays date is within 30 days of being due and finally red if todays date is over. i have manged green and red but cant seem to get the between right for the amber.
So Column 1 I've got dates, need to sort through that and calculate Year-to-date and Month-to-Date values. These are both Sums of the cells.... YTD = Sum of all cells with most recent yr, in this case 2007 MTD = Sum of all cells in Column B for most recent month, Feb2007 here.
I've listed the desired solution for YTD and MTD on the sheet as well. (I'm guessing the solution will have something to do with SUMPRODUCT?)
In the attached workbook, sorting the dates in column M results in absolutely nothing happening. The dates are formatted as dates (dd/mm/yyyy). The dates in column M are arrived at by adding a number of days (formatted as Number) to another date, the value of which was determined by an array formula. When I retype the actual date into another column and sort that, I get it sorted. Why does the other sort not work? BTW - I actually need to sort column M with column N.
Find the sorting method of dates in excel 2010. I have dates in one column and i want to sort it, but I am not able to sort in ascending or descending order.
I have an auto generated report that is exported into .csv, my dilemma is that it sorts both the account code and the date in the same column, like this:
9884A B Gerrish Ltd œ 577.270577.270 30/09/201231/10/20121865518655277.010277.01MAL/553260 30/09/201231/10/20121884218842264.260264.26MAL/553259 30/11/201231/12/2012195541955436036MEL/549200
so "9884" and "30/09/2012" are in the same column.
Some how i need to turn this information to say the below three invoices are for account 9884, into more of a usuable data like:
9884 A B Gerrish Ltd 30/09/2012 etc etc.
Obviously through out the document this repeats for each account, upto some 1000 accounts.
This error occcurs when i add some worksheets to a workbook from another one. I am not completely sure (cos this is not my work actually) but it seems to me that there is not really too much (about 4000?) "different" cell formats in the workbook, but there is a quite lot amount of drawing objects (grouped technical drwaings plus autocad objects which i also converted them to bitmaps to overcome the error).
I also dont understand the restriction: If i have 3999 cells formatted "bold" and another 2 formatted "underlined" this should not count 4001. True?
My workbook has about 15 worksheets with each fits to 2 printing pages.
I have a spreadsheet with a number of columns. In column D there are the following options: 2,3,4,C,T. I want to color the cell if there is data in the cell and column D contains a value.
So for cell L17, my conditional format I have =AND(D17=2,L17<>"") - - (RED), =AND(D17=3,L17<>"") - - (YELLOW), =AND(D17=4,L17<>"") - - (GREEN).
I need to color for values of C and T: but there are only 3 conditional formats permitted. Is there any way around this?
I'm running excel 2000 and don't have access to mscal.ocx, so i have used the date time picker with a calendar, but the formatting of the calendar is in US format m/d/yy and I need it in UK format d/m/yy, I have narrowed it down the following piece of code, the D values are the days,
I have a sheet that shows sun, mon, tue........,sat - all 7 days.
Under each day there are 3 cols so 21 in total.
Data is being entered into cells each day but as the week goes on it gets more difficult to match up the col & row, to many numbers.
What I would like is a way to highlight the whole range of cells per day in a different colour but only when data has been entered under a day, so if no data then no colour fill.
I only need 2 colours, 1 for sun, tue, thurs, sat & 1 for the remaining days.
=COUNTA($A1:$C20)>0
This works for one range as a conditional format but there aren't enough options to do the 7 days.
I need for my office a table which has one column with dates. The "issue" is, that sometimes I know the whole date (day, month, year), sometimes only the month and year and occasionally just the year. Is there a way through custom number formatting that excel behaves correctly? How would I need to set conditions to achieve this? Because now something happens which is 99% incorrect .
I have data sent to me with different date formats on the same spreadsheet; I used Format - Cell and format date to this format: yyyy.mm.dd. However, only some of the data changed to this format and rest remain the same. I've tried many other ways, but didn't work.
I need to create a custom number format for a recorded delivery number in an excel sheet. I want it to look like AA 1234 1234 1GB in the cell even if the user enters it without spaces e.g. AA123412341GB. I have had a bit of a play around with underscores to get spaces but nothing seems to make it change.
I would like to condition one cell to display various colors, based on the information in another cell. I have attached the file for an example. The cell to be conditioned is A1, and the cell that will specify its color is B3. For example, if the value of B3 is "Red Oak", A1 would be red, and if the value is "Maple", A1 would be green.
I have more than 3 of these that need to be formatted (conditional formatting limits to 3 formats), so I'm wondering if I can do an event macro to set the formatting.
I have slight problem with an OnChange Event by Target.
I am looking to be able to change a cell value from one to another, create a comment block that says "person B for person A" , change the color and be done.
If the cell is empty then the code should exit the sub (not working). If the cell has already been change once then I want the comment box to show a second line of who changed what.
What I tried in the comment box, thinking it would keep the value:
I have a userform which allows data to be entered onto a worksheet. One of the fields (a textbox with the standard calendar control) is the date. When the transaction saves, it correctly saves the date as dd/mm/yyyy using
ActiveCell.Value = TxtDate.Text
I also have a form which allows the user to amend a transaction by loading the data onto the form, amend it and save it back to the worksheet.
The problem is that when it picks up the data and re-saves it, it's resaved in the format mm/dd/yyyy which is a problem because I use the month as one of the categories that users can filter the data by.
I also have the following line immediately after each time the data is saved from the form to the worksheet:
Code be to change the number format of the range H21:H37 on my attached spreadsheet from the currency format with the $ symbol and 2 decimal places whenever cell H20 displays "UNIT PRICE" to a number utilizing the comma seperator and with no decimal places and no $ symbol whenever the cell H20 displays "SHIPPED"?
