% Done In A Calender Year
Jun 25, 2009
I wish to be able to calculate the % of a particular task that is done in a calender year based on the task start date and duration.
Columns Headings:
A: Start Date
B: Duration (months)
C: End Date (= Start date + (duration * (365/12)))
D: 2009
E: 2010
F: 2011
G: 2012, etc
Examples:
Start Duration End Date 2009 2010 2011 2012 2013 2014 etc
1 Jul 09 12 1 Jul 10 50% 50%
1 Nov 09 12 1 Nov 10 17% 83%
1 Nov 10 36 31 Oct 13 6% 33% 33% 28%
So there are two inputs and the outputs (%'s) are calculated for each year.
View 9 Replies
ADVERTISEMENT
Oct 9, 2009
I found this code on this forum for a pop calendar. I created one for my user form, how ever I would like the calenadar to pop up when the user places the curser in the text box, when the user has slected the required date the calendar closes and the date the he/she selected is entered into the text box in which they placed the curser.
My userform is called userform1 and the calendar is called userform3
Private Sub Calendar1_Click()
Range("A1") = Calendar1.Value
Calendar1.Visible = False
End Sub
Private Sub CommandButton1_Click()
Calendar1.Visible = True
End Sub
The above code works on a putting the information into cell A1 and not the text box, also it has a command button action, which I don't want
View 9 Replies
View Related
Jan 7, 2010
In my excel I want to use a calender date such as 05/21/09 and automatically add or subtract a specific number of days. For example: 10 days before 05/21/09....
I dont want to have to count back 10 days and manually enter it. Is there a way to have excel calculate and give me the date in my spreadsheet? i.e. automatically calculate (05/12/09)-(10 days)= 5/11/09. its not too hard when its 10 days, but when its -97 days, it can be very annoying.
View 5 Replies
View Related
Jun 8, 2008
The current code which I did can only pop up a caledar when I click on cell D4. Is there a way I can program it such that, if I click on D5-D7 and E4 - E7, the same calendar will pop out also?
Also, there bug which I found in this code, that if I click on D4 once, the calendar will appear. After I select the date, the calendar dissppear. The bug will occur when i try to click on D4 again, the calendar will not pop up. it will only appear after i click on any other cell, then back to D4.
View 10 Replies
View Related
Feb 26, 2010
I have a Form created using the Control toolbox within Excel... The data is then transfer to another spreadsheet using linked cell within the properties i can then copy and paste into a master spreadsheet...
This form is emailed to an individual who then populates and emails it back to myself... Everything is working fine but now i would like to include a pop out calendar... I have managed to create the calendar but would like it to input the data into a Text Box rather than an active cell... Once this date is put into the active cell i can then mirror this back to the other spreadsheet using linked cell in properties
View 9 Replies
View Related
Sep 7, 2006
how to setup calender object in VBA application
View 4 Replies
View Related
Jun 6, 2009
I have made a cash register in excel with a cell that counts the total of all cash coming in per day whenever you push the button with "payed". I have another sheet where there is a column with all the days of the year. What is the correct syntaxis for transferring the total cash to the cell next to the right date in the other sheet every time i push the "payed" button (am I making any sense...?). And is there a way to reset the total amount back to zero automatically whenever the day changes?
View 5 Replies
View Related
May 21, 2007
I'd like to use a calender ifor this. To start, I did the following:
From Control Toolbox - More Controls - I selected:
Miscrosoft MonthView Control 6.0 (SP4)
This placed a calendar on my worksheet.
What I would like to do is the following
1. Calender Display
1a. When a next button is selected in my wizard it moves to a new form. (Got this done)
1b. The Userform has buttons: "Select Start Date", "Next", and "Cancel" (Got this done)
1c. When user selects date button, this triggers the 'Calender' to display.
Currently, ithe calender is still showing, so I first need to hide it.
It seems to be called "MonthView1" and has the following in the formula bar when Control Toolbox is in edit mode:
=EMBED("MSComCtl2.MonthView.2","")
2. Date selection
2a. User selects a date on the calender. (Easy if calender is displayed)
2b. User selects next button in User Form. (Triggers what follows....)
3. Variable assigned from selection
3a. 'Next' hides the calendar.
3b. 'Next' calls a marco that assigns the date selected from calender to a date variable.
sDate = ???.Value where ???=selected value from calender
3c. 'Next' hides current form and moves on to next in wizard sequence (I can do this)
View 9 Replies
View Related
Feb 23, 2005
Some people throughout our company were having problems using the excel form I created which contains a popup calender. Even with the mscal.ocx installed some people were having issues.
I found a popup calender that can be integrated into the form, and doesn't rely on the mscal.ocx. I put this into the form (that MANY people here helped create), and now all of the people that use the form no longer have any issues.
View 9 Replies
View Related
Jul 2, 2006
I would like to use 52 week calender in my cashflow statement, and was hoping to know what the formula woudl be if i start the sheet with todays date. If i start with 2nd July as the date, i want it to work out the correct number of weeks left in the year.
View 5 Replies
View Related
Sep 10, 2006
I have a spreadsheet with many cells that require a date. I have added calendar control 11.0 and running it with the code below. The problem is, that everytime the sheet is opened, the calendar gradually gets smaller and smaller each time the workbook is opened. I have tried resizing through formatting the object, but after opening the workbook several times, it's has shrunk down to unusable again.
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B161:B184,I161:I184,P161: P184,"), Target) Is Nothing Then
Calendar1.Left = Target.Left
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub
View 3 Replies
View Related
May 18, 2007
I am trying to import outlook calender automaticly using vb. I have found this site http://www.kittytours.org/calendarproject/trouble.html that has the macro ImportCalendarData. I try to run it but I get syntax error at the row
With ActiveSheet.QueryTables.Add(Connection:= Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data
and a yellow arrow is pointing at the top line(sub impo...)
View 4 Replies
View Related
Nov 13, 2009
I find this difficult to explain, so have attached an example. I need to total all entries on a calender that are entered after a specific date. EG:
If the given date is 15 Sept 08, I need all values in Sept - Mar to be totalled. I am measuring data on numerous spreadsheets, all others have specific start dates and finish dates, so its a simple a1-b1 to get the number of days, multiplied by costs. This particular spreadsheet is laid out in calender format, hence the problem)
View 5 Replies
View Related
Dec 2, 2008
I have a userform (FrmComp) and in it i have several Textboxes. When i click on any of the textboxes the calender appears but how i i make the calender assign the date value selected on it to the last clicked on textbox? here's what i have:
View 2 Replies
View Related
Jul 2, 2008
I have a calendar form opened by a control button on an excel userform. Is there a way of closing the calendar form as soon as a date is selected, leaving the initial form open?
View 4 Replies
View Related
Aug 17, 2006
I have a data sheet where the user puts the date the activity takes place and the place,reason, and staff. What I am trying to do is once the user puts the data on the data sheet, the data is place on the calender sheet for that month and date.
View 9 Replies
View Related
Sep 25, 2006
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
View 3 Replies
View Related
Dec 8, 2013
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
View 4 Replies
View Related
Mar 8, 2014
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
View 4 Replies
View Related
May 14, 2009
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 Related
Dec 13, 2011
I am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.
View 1 Replies
View Related
Oct 13, 2008
i have the following table of information
Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93
and a list of dates i need to look up the date to see which year it falls in and return the year
View 9 Replies
View Related
Feb 27, 2009
DATE function won't return TODAY()'s year in the "year" slot.
Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?
View 9 Replies
View Related
Sep 29, 2006
I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?
View 9 Replies
View Related
Oct 21, 2009
1. I need to convert a year into a decimal year ie. 1830 into decimal year (I don't have a month, just year)
2 Year/month into decimal year/month
I just not sure what to do, is the year stored as a number/text/date. What should it even look like? Does 1830 display as 1830.00 using excel.
View 9 Replies
View Related
Oct 22, 2007
I'd like to know a formula which can calculate the moving annual total, that is the sum of the last 4 quarters. Now every time the sales from a quarter is known, i have to recalculate the MAT mannually.
View 2 Replies
View Related
Dec 17, 2009
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 Related
Oct 15, 2007
I have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
View 9 Replies
View Related
May 14, 2009
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.
View 2 Replies
View Related
Aug 20, 2013
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).
View 4 Replies
View Related