I have a column A with dates eg: 2012/04/02 and I want to add a value into column B 120 but on a Saturday it must return value 80. So Monday to Friday must return 120 and Saturdays must return 80.
View 2 RepliesI want to have a variable range.
ie. This value M4 needs to change based on the week.
Range("M4").Select
If it's week 1, then it can be say M4, but week two will need data to go to N4.
I have found out how to get the week number from the user. ie.
' Select Week Number
Dim NumSheets As Integer
Dim Prompt As String
Dim Caption As String
Dim DefValue As Integer
Prompt = "What week do you want to get data for?"
Caption = "Week Number"
DefValue = 1
NumSheets = Val(InputBox(Prompt, Caption, DefValue))
If NumSheets > 52 Then MsgBox "Week Number too high"
If NumSheets < 1 Then MsgBox "Week Number too low"
I tried then linking this value saved as NumSheets by:
Dim Rng As Range
If NumSheets = 1 Then Rng = Range("D3")
If NumSheets = 2 Then Rng = Range("E3")
I've got a column with multiple dates and a lookup sheet with the weeks (start date/end date). What I'm trying to do is :
1) check date
2) look it up in the lookup sheet (where it falls between start data/end date)
3) once i find where it falls, return the corresponding week name/number
I've seen a solution using the Median function but I'm not here it would work in my case. link here
I've attached a workbook : Date_Sample.xlsx‎
I have a worksheet that lasts for a year that is something look like this:
Date Name
1/1 John
2/1 Erwin
3/1 Robert
4/1 John
16/1 Erwin
17/1 Erwin
17/1 John
In my report worksheet, I need a formula to count everyone's attendance from their participation dates based on week
Name Week1 Week3
John 2 1
Erwin 1 2
Robert 1 0
I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.
Excel File as follows:
Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)
I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.
I am currently using the =Weeknum(A1,15), so I have a Fri to Thu week. My issue is that I need anything that is done on Thursday after 3:00pm to be identified as the following week.
For example
04/03/2014 2:00pm = week 14
04/03/2014 3:01pm = week 15
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"......................................
My finacial year starts on the 1st of October of each year and ends 30th of September each year.
My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.
I need to calculate the week numbers of a date based on the above two criteria.
I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!
so for my year 07/08:Week 1 was 01/10/07 to 07/10/07Week 2 was 08/10/07 to 14/10/07ETCSo in the attached worksheet - what formula can I use to populate column D - by using the data in column E?
I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)
I have a drop down box that chooses the week number of the year (This is based off of a series of data from another sheet).
I need some kind of formula that calculates the following Friday based on a week number. Say for this year (2012) The following Friday for "week 1" is 1/13/12.
(This is for payroll information and I'm trying to calculate the pay date based of of data from that week)
I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month.
View 9 Replies View Relatedon a macro i use to open, update a file and then save it in an archive.
Opening and updating the file is no problem, but i want to save under a dynamic name in a folder structure. This is a reoccurring task, and this way I update the same file each period but save a copy with the current data in an archive under a different name in the right place of a directory (archive).
My idea is to have a hidden cell in this workbook, where I can have the name calculated by simpe excel-formula, i.e. ="Filename_"&WEEK(TODAY())&"_"&YEAR(TODAY())&".xls".
The file should go automatically into an archive-directory, lets say C:data....archive2007 (2008, 2009, etc)
I want to add the last folder to my filename, so my macro knows the first part of the path and has to go look up the actual name it gets in order to be saved.
So I end up with a cell containting the filename: 2007Filename_35_2007.xls
Now I only need the macro which looks up this name, adds it to the hard programmed path:
C:data...archive
so that the file gets saved under as: C:data....archive2007Filename_35_2007.xls
this is how i start:
Workbooks.Open Filename:= _
"C:data....Filename.xls"
' here the file is updated...
Windows("Filename.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:data....archive'(how do i do this?)'", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
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 am trying to fill a table of the last 12 values for the purposes of creating dynamic charts. I remember last time i used named ranges, offsets etc etc but been too long to remember how.
Ive attached a worksheet to explain it better.
I should probably mention, I want to be able to change cells C1 and C2 to update the values. Everything else wil be rather static.
Attached File : Test.xlsx‎
I would like to calculate the week number of the month based on a date.
Now my days would only include working weeks (Monday - Friday).
Supposed the date is 12/31/2012:
M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan
Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:
M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb
It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:
M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May
It will be considered as Week 1 of May since it occupies only 2 days of the working week.
Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.
I am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
Here is what I have and am trying to do:
I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):
Date:January 01, 2013
Day:Tuesday
I then entered all the data needed below that (B5:E26):
TimeTransactionsTotal SalesAverage Sales
12:0039 $352.97 $9.05
13:0053 $416.98 $7.87
14:0044 $339.42 $7.71
15:0043 $304.96 $7.09
etc.....
In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns
(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns
1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)
2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F
2=S-S
3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)
2=PM (15-2259)
3= MN (23-659)
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 have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
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.
. In Cell B1 I can have =Weekday(A1), which returns 4 in Cell C1 I can have =CHOOSE(B1,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") this returns the day of the week for the chosen date.
However, I wish to remove step 2 and use just A1 and C1 and if I use =IF(WEEKDAY(A1)=1,"Sunday",CHOOSE(A1,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")), which returns the dreaded - #VALUE!
I have a (required) time sheet on which I'm tracking comp time hours. There are 52 worksheets in the workbook for the year (of course), each of which contains daily and weekly hours.
On the summary page, I have the following columns:
2009 Carryover
2010 Wks Worked
Wks x Std. Hrs (Col 2 x 40)
Actual
"Diff (Comp/ OT)"
Comp time taken-2010
Comp time remaining
What I'd like to do is have the 2010 wks worked column (#2 in the list above) automatically add +1 each calendar week, but I can't figure out how to do it.
For example, as of 12:00 AM on Sun, Jan, 10th, the value in 2010 wks worked should be 1. On Jan 17th at 12:00 AM, the value should be 2, and so-on throughout the year.
Is there some script or formula that can be run that would tie in to the calendar date/time to accomplish this task? I'd also like to have a more elegant solution for calculating actual hours than adding each separate "weekly hours" cell from 52 spreadsheets - works fine.
I have a sheet (sheet2) that this week has the formula
Cell B2
=Sheet1!D2
Cell B3
=Sheet1!B2
Next week I want the formula to be
Cell B2
=Sheet1!F2
Cell B3
=Sheet1!D2
And so forth.
Each Column has the Week ending date (a sunday) in Row 1. So D2 represents this week and B2 Last week, until next week when D2 becomes the 'last week' and F2 becomes the this week.
The inbetween letters contain another set of data for those weeks so i will apply the same formula to these.
For example i input in cell A1 April 1, 2014 in cell A2 it must put Tuesday.
View 2 Replies View Relatedjust wondering if its possible to format a cell to display date and week number and if so how to go about it
eg
04/01/09 week 1