i seem to be having a problem formatting the date in my table. I format the cells in the 'date' category to display the date as '14-Mar-01' but only some of the dates in the cells are changed. I have entered all the dates in this format: 01/01/09. Why do some cells change to the correct format and others do not.
View 5 RepliesIt's a given that formatting doesn't alter underlying values in all areas of Excel, merely changing the way cells look. But in the attached I show two pivot tables using exactly the same data with the sole exception that the first one has the date numbers formatted as numbers whereas the 2nd PT has the date numbers formatted as dates.
With the 2nd PT it will accept the Group by month functionality whereas the 1st PT won't.
On the face of it PTs seem to take more notice of the way a number is formatted when it comes to grouping rather than treating the number as a date which it can group.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
I have to excel files
1. Temperature & Humidity Reading
2. Summary
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
View 2 Replies View RelatedIn column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I have a couple of columns, once edited need to update a table in SQL. This is my code
[Code] .....
However it is not taking UK date formats, it is assuming it is an american format, I get conversion of varchar data type to datetime results in an out-of-range value. What should I add to change the format? The format is in UK on the spreadsheet seemingly....
I have an 'existing results table' as per my attached sample.
I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.
I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.
Please can someone show me the formula on my attached sample?
Please see my example in red.
I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
I'm trying to add conditional formatting to a table, however I have never done this before. I would like the values in column AX to light up red if they are more than 1.5 times greater than last month's in columb AP. Attached is a picture of the table.
Capture.PNG
I have a table that I sort by the first column. The first column has section numbers, and I'd like for each section to alternate in colours so users can easily see the end of one section and the beginning of another. I've attached a screenshot of what it should look like visually, as well as a sample Excel chart
View 6 Replies View RelatedA report is written in a massive text file, and i've got my code to shrink it down, tidy it up and spit out a nice pivot table which my manager wants to be pretty colours (colors for US spelling). So far i've go this (which i recorded lazily):
ActiveSheet.PivotTables("PivotTable2").PivotSelect "Division[Numerical]", _
xlDataAndLabel
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Now - what happens if there are no entries for the "Numerical" division? ERROR!!!
How can i get some kind of "IF this division is present - color 35 please, if not, carry on" command? I have experimented til all the cows have come home, but i still end up with errors and freezing.
I need to create a table of dates which is structured as such:
A B
1/7/08 Yes
7/7/08 no
25/7/08 yes
Column A is date, Column B is a binary selection (yes, no)
I then have a separate sheet with rows corresponding to each date in July 08. I need a function which will look up the date for each row with the above date range and return the value of column B.
for example - if the date is between 1/7/08 & 6/7/08, it would return Yes. If the date is between 7/7 & 25/7/08 it would return No.
The above table is not fixed and can have many dates, with no pattern for the dates. I though of using a nexted IF statement but it would become a bit clunky.
Pivot Tables. I've created a very simple one where my Row Lables are Salesperson then date and the second column is Sales. It looks "backwards" to me, because each salesperson's total sales for all dates combined is ABOVE the data by date. To make it worse, there's a line below the name, which looks like it's separating the name from the next few lines! With the next salesperson's name in the same "block" as the previous salesperson's details, it's very confusing! It would make more sense to me to have each salesperson's total be at the BOTTOM of their section!
View 6 Replies View Relatedwriting VB code to conditionally-format the pivot table shown here (I am not inclined to use the Excel Conditional formatting option as it loses the formatting when refreshing the pivot table)
Excel_screenshot1.png
I need a VB macro that reads each value in a Pivot table .. starting with the Col1, Row1 of the Pivot table .
It then matches the value in Col 1, Row1 to the Baseline value for Col 1 that is specified in (Yellow ) .
Note : The Baseline values are not part of the Pivot table area
If the value in in the Col 1, Row1 is less than the baseline value for that column at the top ,it formats the font color of that pivot table cell (say to red) It then moves to read the value in Col 1, Row2 and does the same check .
Once it completes the check on all values in Col 1, it does the same with Col 2 (where the values are compared to the baseline value for Col 2) ... and so on until all the colums of the PIvor table are validated in the same manner .
I have a table that contains +/- 12,000 (A6:R12266) rows and I use several conditional formatting rules to highlight rows where the data changes from the row above in a sorted column for the visual easy of users. The conditional formatting works great however the data is the spreadsheet is constantly being modified and that is where I run into the problem. Whenever a row is inserted or deleted my rule is divided into multiple rules which is tedius to manage and also causes the formatting to appear incorrectly in some cases. I have the table defined as a named table in the name manager and I use that name for the range in the "Applies to" in conditional formatting, but as soon as I choose apply the name is converted to the current range which becomes several seprate rules and ranges as soon as the table is modified.
Formula to define which rows are highlighted
=$F5<>$F6
Range data applis to after chosing the tables name and clicking apply
=$A$6:$R$12266
My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.
I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.
I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?
I'm using Excel 2003 on Windows 2000 Professional.
I am having difficulty with the pivot table I created in that the name of the month is showing the serial number. In my spreadsheet, the data I have is:
cell B3 = 20070501
cell P3 = 05 { formula =MID(B3,5,2) } {}*not an array formula
cell Q3 =May {formula =DATE(2000,P3,1) }
custom formatting in cell Q3 with MMMM to retreive name of month
In my pivot table, I would like the name of the month "May" to be displayed but 5/1/2000 is appearing. What am I doing wrong? Must I always format my data in the pivot table to return the name of the month? Perhaps someone in the Excel spectrum knows of a better solution.
Thanks to anyone who can guide me to sanity.
Whenever I make one with numbers, I get formatting for a number as below
1234567.78(no commas and 2 decimal places)
I normally want the formatting as 1,234,568 (comma inserted, no decimals,), (the last digit is changed just becasue of round off, other wise number in both cases is same).
Now I can double click the field, goto number--> number and then apply this formatting.
My question is whether there is a setting in excel somewhere so that this formatting will come as default (after I create the pivot table each time)?
I'm trying to loop through all pivot fields in a table and change the format to be 0 decimals and comma seperated.
Here's my
I have converted a table from PDF to Excel format....
The table consists of longf columns with rows of data in each column.
Once converted to Excel format, the columns become single cells with say 100 rows in each cell.
My question is this:Can I reformat these larger cells such that each row within the long single cell gets its own single cell.
This would enable me to copy paste the data into a spreadsheet and avoid the data entry....
I am using Excel for Mac 2011. I am trying to conditionally format the cells that apply to each row in the table with the exception of the grand total (listed as the bottom row). Depending on the filter applied by the user, the number of rows can go from 1 to 10. I have seen where some people have applied based on whether the cell showed a sum of some component in the source data. I am looking for this within the Mac version and have not found it. Do I need to write something in VBA to format it after refresh?
View 1 Replies View RelatedI am working on a macro to insert a row below if a cell with a validation list has an item from the list selected. The new row needs to maintain the same format and formulas as the original row. To explain this better I have attached a very generic spreadsheet Called Custom Order. In this example cell A3 has a validation list. If the user selects one of the items in the list... I need the macro to insert a row below and maintain the format and formulas found in row A3.
Also this is posted on another forum. http://www.excelforum.com/showthread...=1#post1893257.........
Was working on this problem for a poster: [URL] ......
I can't seem to get the pivot table/chart to format exactly as I desire. It seems that as soon as I add 'group by hour and day' Excel forces the formatting to AM/PM and I want to keep it military. I want to group by hour, so that data that occurs at 6:00 and at 6:30 are grouped, and I had to group by day so that 6:00 on 1/1 was separated from 6:00 on 1/2.
Attached is a worksheet which shows the desired chart format (not a pivot chart), and the attempted pivot chart. I want the pivot chart to match the 'simple' chart in look and feel. Any attempts to change the formatting of the row labels to 'h' is promptly ignored by Excel.
Note the two tasks that occur at hour 18 (one at 18:00 and the other at 18:20 (you will need to see the formatting to truly see the minutes)). Those should be combined in the pivot table (and they are) and on my 'adjusted' table (where I used SUMIFS).
How do I clear the pivot table formatting? I want just the content of the pivot table, but can't figure out how to get rid of the formatting?
View 2 Replies View RelatedIn Excel 2007, is it possible to copy & paste a pivot table, and have the result look like a pivot table, but not actually be a pivot table? I want to keep the values and the formatting (the colors and borders, etc) but I want it to not actually be linked to the data or have the ability to change with dropdowns, etc. I've tried the usual copy & paste special (values) thing, and the other otions in the paste special box, but it doesn't keep the formatting.
View 10 Replies View RelatedI have a Master Worksheet that I am using to populate other worksheets using the Table function. I would like to keep the format the same as the Master worksheet even if lines get added or deleted. I have seen that others group the sheets together when making the changes but I can not really us that options. I will be giving the Budget to someone else to fill out and they will not know how to do that.
View 1 Replies View RelatedI have a pivot table which shows below... I need to set up a conditional format to highlight the row where all fields are "0"
Company
Deals
Opps
Days since Deal
Days since Opp
Days since Meet
Co 1
1
7
80
20
20
Co 2
0
0
0
0
0
Co 3
1
4
30
30
28
Co 4
3
3
30
30
37
My boss asked me to format a pivot table like this with irregular ranges:
I've had a crack (as seen below and have gotten everything right except the strange group irregular ranges. I'm stumped! I'm not sure if pivot tables even have a function for irregular group ranges.
My raw data looks like here is its layout:
I have to import data into Excel from Access (yes, I have to import it to Excel and cannot use Access exclusively). When I import; however, the formatting does not come over correctly, most especially losing the formatting of numbers. I need one of two solutions:
1.) How to get the formatting to move over correctly?
2.) Or how I can save the formatting of a table, then be able to apply it as a "saved" format? The biggest thing is that the numbers will changed the number of decimal places and/or change from general numbers when I want to see them as currency.
The top table shows how i want this formatted, the bottom shows how it is importing.