I am entering data for ships arrived every month. There are about 200 records to be entered and for every record there are 4 date fields.
My question is : Is it possible to set default month (for ex = July) and year ( for ex = 2013 ) in these 4 cells so that
If I just type 16 it should be 16/07/2013. Because all my dates will be within that month July 2013.
In a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?
A1: 27-July-2012
Answer D1:JULY' 2012
Pl note "' " is suffixed after JULY (the month).
Three columns.
A - Date last checked
B - Due Date
C - Actual Date checked
Currently column B is formatted to Date and simply has =A+84 and will display a date 3 months in future. However if there is no date in column A, then column B displays a default 1900 date.. Is there a way of making this blank if there is no date in col A?
As in the attachment example, I use a database (sheet2, where data start from row 3, and headers are in rows 1,2). I've named it as "Data" with a dynamic range name (formula: FFSET(Sheet2!$A$3;0;0;COUNTA(Sheet2!$A:$A)-2;COUNTA(Sheet2!$2:$2)). When I enter a month and a year, in sheet1, I want to get for that year's month:
1) The working days
2) The sums of the fields Totals and each of the other (AA, BB, CC etc.).
I would like to see the Year To Date sum month by month in cell B8 when rolling down the cell B6. See in attached file.
View 9 Replies View Relatedi have attached a sheet, i want if i put date in cell a2 and automatically month should come in cell b2 and year in c2,
LIKE JAN AND 2009 LIKE THAT
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies View RelatedSerial No Search E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4
E - Year Month Date
I need F column as Month Date Year Format
Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
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 my ms access and ms excel which is connected each other .
=> now in my ms access have a table name (tblMonth) has columns (year,MonthNum,StartDate,EndDate)
HERE: sample in january(1) and feb(2)
YearMonthNum StartDate EndDate
2014 130/12/201305/01/2014
2014 106/01/201412/01/2014
2014 1 13/01/201419/01/2014
2014 120/01/201426/01/2014
2014 127/01/201402/02/2014
2014 203/02/201409/02/2014
2014 2 10/02/2014 16/02/2014
2014 217/02/201423/02/2014
2014 224/02/201402/03/2014
=> and for my ms excel has its columns (year,month,date) in this if i input the date that is base in the database range from StartDate TO its EndDate will automatically put the month and year which base also in my database (year,MonthNum)
HERE:
A1 B1 C1
Year Month Date
A2=2014 B2=January C2=01/01/2014
I'm using a formula to copy a time from one cell to another
across sheets. The format of the time is h:mm AM/PM.
However, when the formula references an empty cell, it puts in a
default value of 12:00 AM and I need it to remain blank, (just as
the referenced cell)
It's such a simple copy formula. ie:
=sheet1!A1
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)
In the workbook the sheet names are month names e.g. july,august,September etc and 1st of each month is to be entered in a cell in each the sheets. By some trial and error I wrote this macro
VB:
Sub test()
Dim j As Integer, monthnr As Long, monthname As String
For j = 1 To Worksheets.Count
[Code]....
This worked in July sheet it is written as 7/1/2013 and in august sheet it is 8/1/2013 etc Then I shortened the macro like thlis
VB:
Sub test()
Dim dte As Date
dte = "july" & "/1/2013"
Range("A1") = dte
End Sub
It worked. Even if I use shortened 3 letter form of month like "Jul" or "aug" then also it works
But if type on the spreadsheet itself
="july"/1/2013
It Gives value error.
Perhaps it works only in vba and not spreadsheet.
I accept nobody is going to use "july" & "/1/2013" instead of 7/1/2013 But this will be useful if sheet names are month names so that when we write the date we can use sheet names Mine excel 2007 and windows 7.
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies View RelatedI am writing a module which have a different test based on for each of the previous 2 column cells. It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i need to use a inputbox for entering the first date for monday of the month each time i use the worksheet.
When i try using the module for each cell of the column it display the input box for each cell. is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again.
Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
View 2 Replies View RelatedI have a sheet where the columns have the month end date for each date, ex January 31,2014 February 28, 2014, March 31, 2014 etc. These dates are used in another formula to compare to the current date and if the current date is past the result is different from when if the date is in the future.
I would like a formula that would update the Month end date when we enter a new year. So for example once we get to January 1 2015 my date would change to January 31,2015, February 28, 2015, March 31, 2015. this would note be dependent on the system date but on the date in another cell.
I have a table with columns 'Date', 'Account', 'Sales'.
I need a formula that will give me the given sales for a certain month, for a certain account.
Ive tried various SUMIFS '=SUMIF(Table1[Billing Month],(MONTH(A191)&YEAR(A191) = MONTH(Table1[Billing Month])&YEAR(Table1[Billing Month])),Table1[Net Invoice Value])'
Ive looked at Arrays =SUM(IF(MONTH($A$2:$A$6)=1,$B$2:$B$6,0))
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 column in a spreadsheet that stores a date and another column that i want to store a date category. I want the "date category" column to return the date from the "date" column as: eg Jan 09 (if the date is eg 24/01/09)
View 2 Replies View RelatedIm having a little issue with the way the dates are layed out.
I have a condition format =AND(RC5="No",TODAY()<RC4+10)
And withen the cell's the date is located, If the cell is writen Month/Day/Year everythink works.
Though i have always written dates like Day/Month/Year.
ive tryed to do go to the number format thing and change it, though that just seams to change the end display not the way excel handles the date. it there a system level change that can be made of change somethink.
I'm getting people to set up the dates as mmm-yy which makes it so much easier to run a macro with dates. However I still need to search on d/mm/yy (e.g. 1/04/2012 for April). Is there a way of allowing the input box to have April 2012 match 1/04/2012?
I've currently got a message in the Input Box specifying that people need to type in the specific first day of the relevant month.
I have a list of dates that has been entered as dd/mm/yy. I need to extract the month in one column and year in another column from that date.
I tried using =Month(a1) and Year(a1) but it returns as #value.
I have data which I import from CSV files from a JDE application on a monthly basis, using "JobCostData" macro ( have not posted code here as it is contained in the attached file).
For each month I import the data my macro creates a seperate spreadsheet and names the spreadsheet as the "mmm-yy" the data relates to, and then takes the information from each sheet and collates this on to on spreadsheet named " Pivot Data".
I then run a second macro "MacroXX", ( again have not posted code here as it is contained in the attached file), this macro looks at the data on "Pivot Data" and creates a unique list of Work Order Numbers and Descriptions and copies them to the spreadsheet named "Report".
Now what I would like to do (and can't get my head around):
Once the "Report" has been populated I would like to come up with a series of total "Cost $" for each Work Order by:
1. Month, for the past 6 months based on a Calendar Month End date (Note: For now I have hard coded the dates, but I will add code at a later stage to prompt user to select a date which will then write this to cell H4 on the "Report" spreadsheet and have the other dates derived from this date.)
2. Year to Date (YTD) based on the Financial year the Calendar Month End Date falls
3. Inception to Date (ITD) based on the
Note:
1. I have tried using Pivot Tables but this will not give me ITD totals.
2. I would prefer to use a macro rather than formulas.
3. I have populated the "Report" spreadsheet with the expect result based on the current data.
I have a spread sheet for 12 months. I am wondering if there is a way to format to show in the cell only day of the month.
View 4 Replies View RelatedI am creating a worbook to generate month end reports. I'd like to pull figures from the various budget sheets into the reporting sheets for the current month and year to date by setting up a variables sheet and I think the "Choose" function but my experimentations have proven me incapable of doing so.
What I'd like to do is set my month in the variables sheet to the month I want to report in, eg "Feb" and have the budgets pull through automatically.
I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.
There is of course several ways to do this, but is there a simple and easy formula one can use.
Need a formula for counting the number of occurences of a month & year in a date column? The spreadsheet is looking at items raised in any given month e.g. all items raised in Dec-08.
View 3 Replies View RelatedMy workbook has a worksheet that lists service activities and a 2nd worksheet that contains a month-year column and an associated Customer PO# to be assigned to an activity based on its Open Date...if it is a specific type of activity.
My logic statement is IF activity type RO is "X", then match its Open Date to the Month-Year in the AssignPO# sheet and assign the associated Customer PO.
I've attached a sample workbook to make it simpler to understand.