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 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 RelatedI 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"......................................
I 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 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'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.
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?
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies View RelatedI am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
I am looking to have one formula containing two If Statements that minus.
=IF(B6="investment",C6,0)-IF(B7="gross",C6,0)
So the result of If Statement 1 minus the result of If Statement 2.
What I am seeing at the moment in the cell is FALSE and what I want to see is the sum.
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24
But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)
Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.
i.e.
Column A --- Column B ---- Column C
AB ------------ 0:02:22 ----- 04/14/2007
CW ----------- 0:03:13 ----- 04/16/2007
CW ----------- 0:01:42 ----- 04/13/2007
Thus, the value that should be returned is "04/16/2007".
I have a problem with a formula inserted with control+shift+enter
The formula is
MEAN(IF(' VALIDATION'!$G$2:$G$59999=D30;'VALIDATION'!$E$2:$E$59999))
In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values.
Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G.
The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D.
I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
How can i find the first day of a week
I have the week number and i need the date of monday of that week
Ex: Week number:24
First day of week(Monday):12-06-2006
I need the name of the day of the week from a date that has to be put together. This is going into a label on a UserForm.
I have a named range called wbDate. It is the month & year. The day of the month is the value of a TextBox (TBDay) on a UserForm. When I tried to combine these items, I used this
vToday = Month("wbDate") & " " & TBDay.Value & ", " & Year("wbDate")
vToday is dimmed as variant.
I am getting a type mismatch error and I'm not sure how to correct it.
What I am aiming for eventually, is the name of the day of the week.
In example file i wrote different dates along the column. How i can i write a filter in Table for showing only Thursdays from those dates?
File: [URL] .....
Does Excel know week numbers? e.g. Week 40 is Sunday 10/5/2008 to Saturday 10/11/2008. Here's what I have...
Column A Column G
date $ Amount
my data example...
10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/11/2008 $8.00
10/12/2008 $25.00
10/13/2008 $75.00
What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...
Week 40 $50.00
Week 41 $100.00
1 2 3 4 5 6
11/1 11/2 11/3 11/4 11/5 11/6
sat sun mon tues wed thur
what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week
=$D$1
=$D$1+E5-1
=TEXT(WEEKDAY(D6),"ddd")
cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.
I created a pivot table with daily dates listed from 1/1/2009- 3/31/2009. 3 months worth. I see i can group this data by hours, days, months, quarters and years but what i really need to do is group this by WEEK. Does anyone know a formula I could use to accomplish this?
View 3 Replies View RelatedCan somebody have a look at the workbook that I've attached.
I have set up a timeshhet calculator for work but the thing is, the hourly rates of pay are dependent upon the day of the week the Operative is working, and the time of the day.
We split the shifts AM & PM and the rates are decided as per the worksheet contained within the workbook "Rates".
The problem I am having is how to get the timesheet to understand the days.
For example, if an Op works the Sat night or Sun night, then the rate should reflect x1.5. If it's Sat/Sun daytime, then it's x1.25.
Again, and as per the "Rates" sheet (see top for the rate multiplier) Mon - Fri 09:00 - 17:00 is flat rate/standard hrs. If the work Mon - Fri evenings, then the rate is x1.25.
How can this be done?
I'm trying to set up a simple macro to loop through some number of dates on a sheet and blank out fields next to every instance of Saturday and Sunday. The only problem I'm having is actually finding the values "Saturday" and "Sunday"
Given a date in this format: 01/01/2009
I can convert to "Thursday"
Problem is, the vba code still "sees" 01/01/2009
I've tried copy / paste special / paste values to no avail.
I've tried changing it via VBA (a few different methods, including strings, formats, etc) with no luck. Even tried referencing my date cells from another spreadsheet and pasting the values back in. Can't seem to get 01/01/2009 to be "Thursday"
I normally use the WORKDAY function in order to create an involved scheduling
program. However, I have no idea how to create a formula that would count
Saturdays as a workday. I usually type in one date and then the dates kick
out to an end date several months later. How do I create a formula to
include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06,
A3=8/15/06,etc...)?
