I have a huge amount of data 34,000 rows and would like to show it as days of the week.
As an example Dates show as 02/14/2008 10:00. I would like to create a new column indicating days of the week and show as Mon, Tue, Wed, etc.
I tried a couple of functions with little luck (weekday, day), so either I'm using incorrectly or I don't understand the help function.
I have a column of dates in one column and in the next column next to the dates, I want to show the day of the week that each date represents, such as;
Col A Col B
1 March 2008 Sunday or Sun
etc etc..
I'm in the process of setting up an Excel document and I need to be able to have it display dates in a specific format. I need it to express just a month and year such that the month is represented by a letter (A thru L) and the year is expressed as its last two digits such as in the these examples:
May 2012 = E12
Mar 2009 = C09
Nov 2011 = K11 etc.
I want the date to come out in this format regardless of how the user enters it.
I have data that shows titles as 2-Feb, 2- Mar; which is really typed in as 2/5/2012 and 3/15/2012. My chart when graphed keeps showing the X-axis as 1-FEB and 1-MAR. How can I get the graph to show the x-axis titleas as 2-Feb and 2-Mar?
View 3 Replies View RelatedIs there anyway for a chart to show only a certain range, ie the past 12 months, without having to update the range every month?
We need to show our scrap rate over the last 3 years. At the end of every month we add a new line. When we enter the number of pieces scrapped for this month I want the chart to drop the number scrapped in January '07
If I have a date which is 03/09/2006. How can I create a formulae to abbreviate this. Ie to return 09/2006 (I am english so we have the DD and MM the other way round to you guys in the states).
In other words if an event happens on the third of september 2006 I want a column which classifies that event as september 2006 with NO reference to the day.
I have the below find and replace code that is now working. I double checked the syntax and can't find a reason why. The errors I get are Overflow and 400.
'format column with custom date format.
Public Sub formatDate()
Dim charHold As Date
What = m / d / yyyy
repl = yyyymd
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
dates column displays numbers even though the format in the format cell options is date format.
the number updates to date format only when i do F2 & enter for each cell. i've about 5000 cells with same problem. how cani do them all in a go instead of doing themone for a time.
i would like to display all the employee whose passport and visa is almost expired (30 days before expiry) to another sheet. I have done conditional formatting highlighting both fields 30 days before expiry but i want to display the list on another sheet.
View 2 Replies View RelatedI have the months in a drop down menu January to December in cell B1.
Is there a way for excel to display the dates in that particular month based on the selection in cell B1?
For example, if I were to select "February", I'd like Excel to display:
02/01/2012
02/02/2012
...
02/29/2012
in cells A5 through last day of the month?
I would like to use a formula to take the starting date in cell A1 and then have Excel give me a list of all the dates for 2009 excluding Sundays.
So A1 would be 1/01/09
Then Excel would list
Thursday, Jan 01
Friday, Jan 02
Saturday, Jan 03
Monday, Jan 05
Tuesday, Jan 06
etc....
I've tried messing with the WEEKDAY function, but can't seem to figure it out.
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
This also includes the WHITE sheet.
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.
What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc
I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
In Cells B2:B100, i have dates that which have been entered using a combo box (the dates type is for e.g. 14th March 2010 format)
I want a formula that will count the cells that have dates between 1st April 2010 to 30th June 2010 in cells B2:B100
Also, I would like a formula that counts weekend dates between 1st April 2010 to 30th June 2010?
I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.
View 2 Replies View RelatedI have a masive table of dates (the date is created via a if formula)
what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.
Dates To Test 14/08/2026 19/08/2026
Results
14/08/2026
15/08/2026
16/08/2026
17/08/2026
18/08/2026
19/08/2026
Table..............
i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies View Related