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 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.
I can retrieve the weeknum of any particular date using =WEEKNUM(x) or =TRUNC(((x-DATE(YEAR(x),1,0))+6)/7)
But I'm looking for a UDF to output the tax year week number where the tax year always starts on 6th April. The std weeknum for 6th of april pretty much varies around week 13 but as it varies I don't ever get a strictly true result by deducting 13 from WEEKNUM().
I've done a search on here to find out how to convert a date to a week number & found this: - =WEEKNUM(A1) which works fine, But I also want the result to display the year.
I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.
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)
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost Col B = The number of the month when the costs are starting (1=Jan etc) Col C = The duration or the number of months for which the costs are to spread Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
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)
on 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 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.
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 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 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
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 have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:
Start Range End Range Qty 2101200 2101499 300
I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc. but some time these numbers are in qty 30000 or more. I have questions
1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.
My Second question is opposit of my above question.
2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example. Series [b]Start Range End Range Qty 1 1 1 1 3 3 3 1 5 5 7 3 6 7
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 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.
My intentions are for Excel to recognize a series of dates as a particular week in the year. For example: 12/28/2008 thru 1/3/2009 equals Week 1, 1/4/2009 thru 1/10/09 equals Week 2.
I cannot for the life of me calculate a working formula.
This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.
What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks.
I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)"
B1 is the current days date B2 is the entered date
What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).
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 ?
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.
I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.
I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.
------ UPDATE
In response to using CountIFS I have also tried-
=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.
I need to calculate and compare the extra number of hours worked less lunch time (0.5h per day) "per day sum by week" and total "per week" :
- number of hours above 40h per week; - number of hours above 9h per day sum by week; and keep the higher.
I've nearly found the formula to have extra hours for total week =SUMPRODUCT(($B$3:$AK$3),--ISNUMBER($B$3:$AK$3),--(WEEKNUM($B$2:$AK$2+0,2)=AN1))-(SUMPRODUCT(--(WEEKNUM($B$2:$AK$2+0,2)=AN1),--ISNUMBER($B$3:$AK$3))*0.5)-40
But for total week I don't know how to sum per week hours above 9+0,5 per day
