Chart With (dd.mm.yyyy H:mm:ss) Data
May 5, 2006
I have folowing problem:
I have delivery date (C) and wenn has been delivered (D= Start, E=End)
sometimes Delivery End (E) is on day after Delivery Start but in my chart
you can't see it, what I try to do is to display if Delivery End is not on same day as Delivery Start, so I need to display Delivery Start as negative value (below 0)
but my chart goes from 00:00:00 to like 04:48:00 next day.
in Excel workbook:
Delivery Start - Green
Delivery End - Blue
Chart date is automatically generated (Oracle)
View 9 Replies
ADVERTISEMENT
Feb 25, 2009
I have a 20,000 line Excel sheet...
And all the dates are entered like this:
2009 02 08
I want to add a dash, so they look like this
2009-02-08
so I added a command button, and this code, but its not searching the sheet and adding the dash's...
View 14 Replies
View Related
May 1, 2014
I have the macro below which opens csv files stored in a user selected folder and processes them changing the date format in column D from DD/MM/YYYY to text stored as YYYY-MM-DD.
For most of the dates the code works without issue, but for some (those with a month <12 possibly) it transposes the MM and DD incorrectly.
I understand that when opening the CSV's in excel it automatically converts the dates to DD/MM/YYYY, so I'm actually opening in wordpad which displays as YYYY-MM-DD, with only a portion being incorrect.
I've attached a couple of sample files (pre and post conversion).
Status_20post.csv‎
Status_20pre.csv‎
View 9 Replies
View Related
Mar 20, 2014
creating a user form that will make my job of recording data far easier. However, when the date is entered, it changes it form UK to US.
I've checked the cells and they are formatted for UK dd/mm/yyyy.
The script is as follows:
Private Sub UserForm1_Click()
End Sub
Private Sub DataInput.UserForm_Initialize()
[Code].....
View 2 Replies
View Related
Mar 20, 2014
I want to convert to date from DD-MON-YYYY into YYYY-mm-DD format in column J.
It is also applicable to date separated by semicolon. It was working previously but not now.
HTML Code:Â
Sub DATEE()
Dim rng As Range
Dim rngTotal As Range
Dim i As Integer
Set rngTotal = Range("j1:j" & Range("j" & Rows.Count).End(xlUp).Row)
[code]....
I made one mistake . date conversion should be 2014/03/11 if orignial date is 11-MAR-2014
View 4 Replies
View Related
Mar 3, 2009
I finally finished programming the userform that I've been working on for about a week and started to populate the database with info (dates, names, TRUE/FALSE etc etc).
Then all of a sudden I notice that as I switch between "records", one of the dates gets a life of its own - it changes from dd/mm/yyyy to mm/dd/yyyy as I select it. When I jump onto another record and select that previous record again, the format changes from mm/dd/yyyy to dd/mm/yyyy.
This happens with ALL dates where the days and months are =< 12.
I thought I'd apply formatting to the text box to stop that happening with the code below:
View 3 Replies
View Related
Jul 7, 2009
I use MS Excel 2007. When I type in date as 2/3/2009, it gets typed as 3/2/2009
i.e. DD/MM/YYYY to MM/DD/YYYY. Attached is file for your information. I have even formatted the Cells in that particular column to Date Format under British (UK) DD/MM/YYYY. I want dates in DD/MM/YYYY format only.
View 2 Replies
View Related
Feb 20, 2014
I need to change mm/dd/yyyy to dd/mm/yyyy.
Going to format cells and custom Excel thinks the data is already dd/mm/yyyy.The data I have is historical so the date 1/03/2014 has not happened yet so I need to change it to 3/01/2014.
View 6 Replies
View Related
Mar 28, 2008
I have a column currently containing dates in the following format: mm/dd/yyyy
I want to sort these records sequentially from latest to oldest, so I need to convert the data into the following format: yyyy/mm/dd
how to convert this data.
View 14 Replies
View Related
Jul 16, 2009
I need to change a whole column of dates shown as “dd.mm.yyyy” to “dd/mm/yyyy”. I know the answer must be quite easy but it’s been bugging me for some time. Even after sorting the cells, it only sorts using the first two digits, totally ignoring the rest. IE, it sorts 01.07.2009 before 15.05.2009, also before 03.02.2008.
View 3 Replies
View Related
Dec 3, 2011
I have a worksheet with dates as Mon/day/year ( 05/02/2011- May 2nd )
but the template worksheet which i have been told to use, has all dates as day/mon/year. (so above date should read as 02/05/2011)
When i try to use format painter to convert my mon/day/year to day/mon/year, it doesnot work.
Neither does the trick of using text to date format using technique shown in this board worked ( text to columns, fixed , YMD )
So how do i get my dates changed from mm/dd/yyyy to dd/mm/yyyy?
View 5 Replies
View Related
Sep 5, 2012
Here is my VBA code so far, it sorts relevant email with data in it into spreadsheet titled "Mon 1st" "Mon 2nd" "Tue 1st" "Tue 2nd" ... and so on
But I've changed the name of the spreadsheet into "yyyy-mm-dd" format, amend the codes.
olMDate = olMail.SentOn
'Amend this line
wsDest = UCase(Left(WeekdayName(Weekday(olMDate) - 1), 3))
If TimeValue(olMDate) < #12:00:00 PM# Then
wsDest = wsDest + " 1st"
[Code].....
View 1 Replies
View Related
Nov 12, 2013
I want to add some Data Validation for a cell and want it to validate that the entered data is in the format (DD/MM/YYYY HH:MM)
I guess I need to use a custom validation formula, however cant figure it out
View 1 Replies
View Related
Jan 14, 2014
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
View 5 Replies
View Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Dec 29, 2011
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
View 1 Replies
View Related
Mar 26, 2009
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
View 4 Replies
View Related
Jun 30, 2008
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View 9 Replies
View Related
Aug 9, 2009
I have around 1900 dates that I need to sort into order but I need the data to be displayed as DD/MM in chronological order eg 01/01, 02/01 etc.
At the moment the dates are DD/MM/YYYY in one column. Have tried sorting them into date order but have found that they're sorted by YYYY. Is there a way of sorting them DD/MM instead?????
View 5 Replies
View Related
Sep 24, 2009
What is the Easiest way to Convert this Date and time format into Dates Format i.e MM/DD/YY
View 5 Replies
View Related
Nov 1, 2006
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
View 3 Replies
View Related
Oct 3, 2008
I need to import some csv files but I can not properly manage one of the field because it is a date and that field behave in a way that I can not definitely control;
I got an inversion of month with day so that the final layout is mm/dd/yyyy instead of the correct date layout dd/mm/yyyy I want (need);
the main problem is that when I'm importing these files via a VBA macro like
Workbooks.Open Filename:="n42s3.csv"
the inversion does happen whilst when I'm going through the "normal way" of the menu File, Open... it does not;
and moreover even if I put some commands on the vba macro like
Selection.NumberFormat = "dd/mm/yy hh.mm;@"
in order to control the correct date layout I want I get no result at all
it's a big problem for myself because I need to open a large number of files which can not conveniently handled with the normal way (i.e. passing through the menu)
View 9 Replies
View Related
Aug 24, 2007
My spreadsheet imports a date field and displays it as YYYYMMDD.
In A2, I use the formula =Right(a1,2) & "/" & Mid(a1,5,2) & "/" & Left(a1,4) and this converts it to DD/MM/YYYY
However, I can't find out how to write this into my VB Macro.
I've tried:
Range("a1").FormulaR1C1 = "=RIGHT(RC[-1],2) & ""/"" & MID (RC[-1],5,1) & ""/"" & LEFT (RC[-1],4)"
View 8 Replies
View Related
Apr 3, 2014
I have to count the number of hours within a cell which has also dates. Example below.
12/03/2014 04:00
12/03/2014 04:00
13/03/2014 07:00
What is the formula to count the number of hours given the date ranges need to be in the same cell and the dates and hours are likely to change ?
View 6 Replies
View Related
Oct 22, 2007
I'm looking to display the weekday in a cell, based on the date in cell A1. I know the code I can use is
View 5 Replies
View Related
Oct 22, 2007
I have a file that I want to change the format for my date field. Currently the cells are in general format. How can I put them into a date format. Example the first one is 20070328, how can I make this 03/08/2007?
View 5 Replies
View Related
Feb 25, 2009
I have a list with several thousand dates listed like:
19931014
19900724
19910701
19911209
That is, yyyymmdd.
I would like to list it as yymm only. Is there a way to convert it in Excel? I have for a long time been copying and pasting in to Word then doing a search and replace of the first and last two digits, which results in the yymm format I am looking for. A better way would be nice.
View 3 Replies
View Related
Aug 9, 2013
I have worksheet of data that I need to sort by year then month. Currently when I try to sort the data by data excel sorts by the month then year. how to reverse this so the dates are sorted by year then month?
View 1 Replies
View Related
Aug 18, 2013
Is there a way to lock the date format as MM/DD/YYYY. I'm using the excel file as data file but one of the softwares fails to run this excel-based data file due to date format since the date format has to be in numeric format.
1/12/2013 changes to 12-Jan while processing? How to lock this format?
View 7 Replies
View Related
Jun 30, 2014
I need to take 3 columns (G, H, I) and format the dates that are in standard format to mm-dd-yyyy.
The code I have looked at seems way to complicated and I couldn't get it to work
View 2 Replies
View Related
