I'm looking for away to convert the value of cells with a numbers that represents the days of the month as 1 - 31 to the full date.
As if the cell value is 3 convert to 3/3/2014 and so on. I tried changing the cell format which does covert it to 1/3/1900, then I experimented with DATEDIF but didn't get anywhere.
A spreadsheet im currently working on has the following formula
=SUMPRODUCT(--('Data Entry'!G2:G6000="AAAA"),--('Data Entry'!L2:L6000="Jun 14"),--('Data Entry'!M2:M6000))
It works fine, apart from the bolded text. In the Data Entry worksheet, column L has dates in the format dd/mm/yyyy. I'd like to get any date from June to be included without manually changing every date in June to Jun 14.
I think theres 3 ways of doing this, any of which would be great. Heres what I think. Where "Jun 14" is searching for exact text, I thought maybe I could change it to MONTH=6 or something to pick out June direct from a dd/mm/yyyy format. I couldnt get this to work. Know of anything I could replace "Jun 14" with?I tried to change the format of the entire column L in 'Data Entry' to a 3 letter month and 2 letter year. how to change the cell date format to fit this?Or if I changed the format of the dates to "01 June 2014" could I change my formula to try and just pick out the specific text "June" ignoring the day and year?
I know there must be a way how, but I can't seem to get it right.
The other option I thought of was to create a new column in the Data Entry sheet using the formula =MONTH(A1), then changing the formula to search for "1", "2", "3" etc for each month. I didn't do this as for the purpose of this the exact date is NOT important, only the Month is.
I am trying to use a pivot table to analyse some data. The base contains a "day" date..ie 01/05/08, however I want the pivot table to summarise by the month..ie May 08. I have used the "=month(cellref) command, and it returns the correct month number. However when I try and format this to "mmm" or "mmm yr" to get a month I can then cut and paste, it always retunrs the month of JAN.
View 3 Replies View RelatedI have a cell which says November , 2012 in General format
In the next cell i want a date of the following month . Eg : 1-Dec-2012.
I tried =DATE(RIGHT(G4,4),LEFT(FIND(",",SUBSTITUTE(G4,",",",",1))),1)
My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.
How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.
I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.
If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.
I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?
I'm trying to streamline some processes at work and have encountered a bit of an issue.
I have a source file containing a grid of data (9 columns) with a series of dates they relate to down the left side e.g. 13-Feb-2013. This data covers a whole year.
What I want to do is pull through data relating to a discrete month into a grid in a seperate workbook.
I have a feeling that date ranges and possibly some kind of lookup might be useful but am struggling to see how to pull it all together.
Assuming all my date inputs are in column A, week in B, and month in C.
I found a formula from a forum in converting my date inputs to week number, and here is the formula in column B:
=IF(A40,"WEEK"&INT((A4+1-DATE(YEAR(A4),1,1))/7)+1,"")
- INT(bold) formula part, as those part I just copy paste it, add this and that, and it works
- the reason why I add the "+1" on the very last part is because on my weekly salary payment, every Saturday salary will be paid on the week after.
(ex: 04/28/12 is supposed to be WEEK17, but then I added +1 so it became WEEK18. The main reason is 04/28 is Saturday and like I said I need every Saturday salary to be paid on the week after)
So, I've got the solution to convert my date inputs to Week#, but then I also need this week# to be converted into "MONTH" in a way that the last day/s of any months, but still in the same week of the new month, to be grouped together.
- For an example, 04/28/12 is supposed to be considered as April month, but I need that day to be on the May month.
i'd like a formula to change a date to month and year
Column E
11 May 201123 November 201108 July 201103 September 201111 March 201224 December 2011
I've used =Text(e1,"MMM") to pull the month through but would like to include the year too.
I have weeks in format YY-WW (this week is 09-18) and wish to calculate the full date for the week commencing day for that week. '09-18 is in cell S8. To work out the date I currently have: =DATE(LEFT(S8,2),1,RIGHT(S8,2)*7-8)-WEEKDAY(DATE(LEFT(S8,2),1,3))
Which I thought was working fine, but turns out to be the WC date for 1909. I'll probably have to change the -8, that's not a problem, but I can't work out how to get it to 2009.
This formula looks at a cell and replaces the full date with jst the month
and the year....but some cells are blank, in this case this formula sends
back "100"
I need to add something to this formula that when the cell is
blank...instead of putting "100" as it does now, I need it to put
"Unscheduled"...
Like "If 100, then "Unsdchedule"
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies View RelatedI have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
Is there a formula to set the a date in Excel based off the next full calendar quarter? In cell G2 I have the contract date: 01/26/06. I would like to have a formula in I2 that displays the first day of the next full calendar quarter, which is 04/01/06
More example dates:
if my contract date is: 10/21/05
my start date is: 01/01/06
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.
How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?
I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.
I am using Microsoft Excel 2010.
How to correct this formula to get correct output.
=IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),
"January","February","March","April","May","June","July","August","September","October","November","December"),""))
It's working fine if the year is 2014 but not if the year is different.
Conditions:
If the date is today then only the month should display else blank. The year should be ignore.
For example:
08/01/2000: January
01/01/2000: blank cell
08/01/2014: January
01/01/2014: blank cell
The date is in column 'p' in dd mm yyyy format. In short if the date is current date that is for today '8' then only the month should be display in the output.
I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:
View 3 Replies View RelatedI have a list of dates stored via the following :
20100101 how can I convert this to date/time format to show 01/01/2010?
I have a program that exports the date as 20120621 (4 digit year, 2 digit month, 2 digit day). I have tried =datevalue(cell) but it returns #value!. I have tried =today()-(cell) and it also returns #value!. Is there a formula or something I can do to covert this field to a date?
View 3 Replies View RelatedI am trying to create a graph where the date starts on July 1st and runs through a full year to June 30. No matter how I sort the dates in the cells, the graph still wants to start in January. You will see from the attached picture the very right lines up with the very left, where these should actually be meeting in the middle. as to what I need to do? Please see photo and dataset.
Chart.jpg
DataSet.xlsx
I am trying to convert a long list of dates from text to date (see A) so that I can sort them from newest to oldest. I've tried converting them using differnt formulas (see B:C) but these don't work.
View 8 Replies View RelatedI would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
I have an export from a database application in which I have thousands of dates. The dates are not in a date format (normal problem) so I wrote a formula to convert them as follows.
Original Date in A1 : 11/13/2008 This is mm/dd/yyyy
Formula : =VALUE(MID(A1,LEN(A1)-6,2)&"/"&LEFT(A1,LEN(A1)-8)&"/"&RIGHT(A1,4))
Result : 13/11/2008
This seems to work at first, until I looked more closely. If I change the date I am converting so that the date in cell A1 is 11/12/2008, ie. the dd value is less than 13, then the formula no longer works. It is fine for all values over 12.
All I get in these cases is #VALUE!
I am currently converting a date in column "A" to a day in Column "B"
Columns("B:B").NumberFormat = "dddd"
Range("B3:B" & lrow).Formula = "=A3"
can someone please advise what code I need to make the day stay as text only in column "B"
If I do a copy / paste special for column "B" and change it to text it reverts to the number of the date.
I would like it to stay as the day,eg, Monday, Tuesday, etc.
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I have a HTML table with a column with numbers like
3.6
2.5
1.8
....
and so on...
When I'm trying to open this table in Excel (.csv or if I cut n paste from HTML) Excel keeps converting it to date format.
03.jun
02.may
01.aug
This wouldn't be a problem if I could only go to Format -> Cell and choose Standard or something.
The problem is it ends up like this then:
39236
39204
39295
WTF? :D
I want this!
3.6
2.5
1.8
....
I have imported data that is showing dates as 18th Sep 2013. Excel does not like this and I have tried various methods of conversion but nothing seems to work. I will be getting data that has Oct, Nov, Dec etc so the formula needs to be able to handle these too. I would like these 'dates' converted to excel friendly dates.
View 7 Replies View Related
