Work shifts run on a four-week, 28-day cycle always beginning on a Monday.
In 2009, the first such cycle began on Monday 12/1/09.
In my 2003 worksheet, I have the current year in A1. I want the date of the beginning of the first cycle in that year to appear in B1. This should update automatically when the year in A1 is updated.
I am trying to get a sheet to automatically calculate week numbers from a given date (start) and the current date, the problem I am having is I only want to cycle 18 weeks not 52.so weeknum wont work
ie
10 weeks after start date would return Week 10
18 weeks after start date would return Week 18
19 weeks after start date would return Week 1
25 weeks after star date would return Week 6
37 weeks after start date would return Week 1
etc...
I'm trying to combine monthly calculations with "today" and with "workdays"
Example:
start date = 01/01/2009
today's date 09/16/2009
formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)
=edate gives me a month but it doesn't skip weekends or calculate beyond today's date
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
I have this column with a value like 04/06/2008. I will want another of the column to auto generate the date for the start of the week like 02/06/2008 which is a Monday.
I am looking for a function or formula that will return the date of the first Monday in any given month and year.
View 4 Replies View RelatedI need to select the highest value at the start of the week as this will represent my starting Inventory level. The start of the week could be any date between the beginning and end of the week. For example, If I only have one data point for the Inventory and it is on a Thursday, then that is the one I want. I went ahead and did the formula to create the week number against the dates
Input Data:
Date Week Inventory
1/1/08 1 780
1/1/08 1 760
1/1/08 1 800
1/2/08 1 500
1/3/08 1 790
1/10/08 2 500
1/10/08 2 650
1/11/08 2 400
1/15/08 3 295
1/17/08 3 275
Output Data (what I need):
Week Inventory at Start of Week
1 800
2 650
3 295
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 am trying to code a macro call where in once the file is saved with the current week start date all the other macro's should be disabled. Since am having lot of data and report sheets which needs to be added and deleted in the run. It causes me problem when I try to open it again to view the results. For example I have a sheet named apple and another one applereport. I have made my code to delete apple since it is a data sheet. I want apple report to have all the other macros disabled once its been renamed to week start date for now it is 16 Mon 2007.
View 3 Replies View Relatedi have a drop down list in cell b16 it has suppliers names in and it has several which are not all one word ie "monk and crane" the thing is that in cell d29 i have a drop down list that is dependant on whats in cell b16 in cell b16 i am using another data validation list using the code
View 2 Replies View RelatedI have a weird problem with Excel. It recognises all numbers as numbers but excluding the number 1. It is only recognised as text as well as a decimals, for example 3.4. So every time I try and add these values up it completely ignores 1 and decimals.
Have I modified a setting?
I have a workbook with three worksheets - 1st is an event register, 2nd is a calender (manually done, with no formulas due to format problems I couldn't solve), 3rd is a summary.
I am trying to work out the following;
The first column of the 1st sheet (event register) requires a date.
The second column requires an 'event', from a drop box or list (of seven possibilities). These are working fine.
Is there a formula I can use theat when a user enters a date, say 1 Jan 08 (or the format is "Thursday, 1 January 2008") AND a certain event, say "CALL" that this will automatically populate the next worksheet (calender) on that corresponding date (1 Jan 08)?
There are seven possibilities for each date (eight including "blank" or ""), which I am sure I will be able to extend to each day of the calender once I work this formula out.
I have already formulated the second worksheet (calender) to count the freqency of events into the third worksheet (summary). Hopefully these wont effect the formula I am looking for.
how I can have excel determine what array or predetermined group a piece of data belongs to as it loops through it.
For example...
Say I import the A column via .csv file, I want to then run a macro that recognises the data and then catergorise it... the desired outcome would be the red text in the C column.
I want to be able to added the variable members to each group as such, I'm presuming this is best by a declaration.
I would like to make the SUMIF function conditional on recognising 1 word among a series of text within a cell.
For example, SUM all cells containing word PINK, even if there are other words in the cell also.
I cannot get the SUMIF function to pick words out of a cell, it will only SUM up if the whole text matches what is in the cell.
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"......................................
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 have this code ...
View 13 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've recorded a Macro that goes through multiple spreadsheets and refreshes the pivot tables in the sheets. I would like the Macro to run without it actually showing it go to each sheet and showing the pivot table updating. Bascially I would like the spreadsheet to remain on the first sheet while it's working with all the spreadsheet in the workbook when the macro is running.
View 2 Replies View RelatedI have a table, 2 columns by 10 rows (A1:B10). The table values are the result of calculations (imagine they are random). These calculations are performed repetitively for some specified number of repetitions (let's say 10 iterations). (e.g. all of the table values change with each iteration until the 10 iterations are complete. With each iteration all of the values in the table change). At the completion of the 10 iterations (one full cycle) I want to know the minimum and maximum calculate value as the 10 iteration cycle was performed.
To simplify; the table changes 10 times in one cycle. I want to know the minimum and maximum values attained in the cycle.
It is easy to determine the minimum and maximum values of the table for each iteration. It seems the problem would be write some vba code that 1) found the min. and max. 2) save that value 3) find the min and max in the next iteration and compare the old and new values 4) retain the value or replace the min and max values with new values and 5) proceed to the next iteration until the cycle is complete.
So I have a cycle time formula; Start Date to Completion Date, if the task is not complete the completion date field is blank. In this case the cycle time is listed as a negative 5 digit value. The networkdays formula takes into consideration weekends and holidays.How can this field be left blank rather than the negative value?
=NETWORKDAYS(I2,P2,Sheet3!$A$2:$A$10)
I want to combine cells from two adjacent columns in this way: a1 is combined with everything in column b, then a2 is combined with everything in column b, etc. So that I have a1b1, a1b2, a1b3, a1b4, a2b1, a2b2, a2b3, etc.
View 9 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.
I am trying to cycle ALL open workbooks so that I can choose the file to manipulate in subsequent code this is the code that I am utilizing furhter below
this is my code
VB:
For Each wb In Workbooks
If MsgBox("Do you want to do access this Workbook for the Update " & Chr(10) & Chr(10) & wb.Name, vbYesNo) = vbYes Then
wb.Activate
VI_wb = wb.Name
I = True
End If
Next wb
But for some reason it only loops through .xls and xlm workbooks BUT not .xlsx
is there a way that I can loop through any and all open workbooks.
The reason I do this is because my update are based on many numerous excel workbooks with differnet extentions, and naming conventions and many of the come via email, I cannot use the eact naming convention
I am seeking to analyse loading cycles on a beam.
I have half hourly data for five years, in two columns A: Date and B: Load in Kg. The load on the beam is not uniform over time and shows a cycle of load.
In Column C I have an AVERAGE function that returns the average load based in the 4 hour period (2 hours backward looking and 2 hours forward looking) if it is less than 50% of the maximum load.
The data in Column C returns sets of continuous vertical cells of data showing periods of low loading and empty cells where the load is greater than 50%. The sets can be as small as one cell (i.e. one hour) to a set of contiguous vertical cells for one week of low load.
My objective is to use Excel to analyse all the sets of contiguous cells in Column C to determine the time period from the first cell of each set to the last cell of that set and drop the result into another work sheet (pivot table) to further analyse these results.
There are 2 buttons on my worksheet below each other. Each button has a size of a range (i.e. "G33:G34", "G36:G37").
This is what I would like to do:
1. Click on the first button
2. Copy A33:F42 and insert them below row 33
3. Move the buttons to the same position (10 rows down) on the sheet
4. Copy the existing buttons and put them in their original space (i.e. "G33:G34", "G36:G37")
5. Do it as many times as I click on the first button
I have this, and I have no idea to the remaining part...
Private Sub CommandButton1_Click()
Range("A33:F42").Select
Selection.Copy
Rows("33:33").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("B33:B42").Select
End Sub