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
I was wondering if there is an easy way to convert a cashflow present quarterly into a cashflow presented monthly? I would like to divide the quarterly figure by 3 and insert each third into one month on the monthly cashflow.
In Cell H2 I have 01-Apr-12 and in cell H870 I have 30,000. I would like this to go into cells H900, H901 and H902 as 10,000 in each. This process could then be copied along.
I have a project I am working on that I need to be able to determine if the date is a Monday. If it is I look at date for the previous friday, if not then just the previous day.
Example with psudo code ....
I am looking to create an invoice details in excel where it correlates to the payment schedule. and the schedule is biweekly starting jan 3rd
I want to also match the start dates to each invoice date, where the invoice date is every 7 days (on a weekly basis)
But, I'm looking to have the invoice date driven in accordance to the employees start date
I'm doing this for a friend, but i'm not sure exactly what he is looking for, and what I have to do in excel
Does he just want me to keep on filling out employee 5 to finish the process?
Or should I create a separate column for each employee for it to be more detailed?
The Overall goal is to get an accurate cashflow
Attached can see what excel sheet looks like : excel.jpg‎
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.
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
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
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.
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.
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
how to setup calender object in VBA application
View 4 Replies View RelatedI was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
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 RelatedI'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)
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.
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
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...)
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)
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 RelatedI 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 RelatedI have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
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 RelatedI'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies View RelatedI have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
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.
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)
View 9 Replies View RelatedI am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
