In column A, field 1 i want to post the text "Week 2, 6-10 Jan" with 5 days in the week. Field 2, "Week 3, 13-17 jan" And then i just want to pull this field down in the column, but then the week number changes and the date interval.
View 1 RepliesAttached is a sheet I use for my pubs to send me their financial information.
Unfortunately, like most staff - they become lazy and need to be kept in line, but when it comes to money, I need it all to work out exactly.
The sheet seems to almost perfect for what I need now, but for the week commencing date on the bar business sheet.
This is the master date for all the figures inputed so I need the week commencing date to always be correct from a Monday.
At the moment the formula I have in ensures that, but when I open it the following Monday to get the figures the date changes to the following Monday from when the sheet was started.
So...is there a Makro that can run that when you open the sheet for the first time it will ask for the week commencing date which will then lock that date or would it be better inserting another sheet called calendar with week numbers and do a lookup function so that a specific week number relates to a specific week commencing?
I want to create a row for each of the 52 weeks in a year. I want the weeks to begin and end on thursdays. Is there a way to automate this so that I do not have to maunally enter in each week? I need to do this for 2008 and 2009.
For example
7/31/08 - 8/7/08
8/7/08 - 8/14/08
8/14/08 - 8/21/08
etc....
I need to average data following the format below by average per week by week/store combination e.g. wk 1/store A average is 6.
Week
Store
Sales
1
A
7
[Code] ..
I can easily use SUMIFS to achieve this, but I have a large amount of data between the weeks of 1-52, a dozen different stores and I will be adding to this. I don't want to have to enter new SUMIFS every time I enter a new wk/store combination. How do I get this info to automatically calculate?
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 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 have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.
I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.
Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.
I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.
this was the macro that was created
Code:
ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
Range("A59").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"
Range("A60").Select
is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.
View 3 Replies View RelatedI have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.
View 5 Replies View RelatedIf a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.
View 3 Replies View RelatedIs there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.
View 4 Replies View RelatedIs 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 Relatedhow can you automatic the date to today's date when you open the excel file?
ie.
Price Report For 02/25/09
i have my data into sections of finanical week number.
but i want to then have a lookup to each week to clarify what week im referring to.
ie. week 41 - 04/01/10 - 10/01/10
is there a formula that will bring this?
Is it possible to format in a macro such that dates that you extract from another workbook can be reformatted in such a way: mm/dd/yyyy Day, for example, :03/23/1990 Sun?
View 3 Replies View RelatedI'm working on Excel sheet which cover period of time, I specify the period on the top, (cell E4= FROM : cell G4= TO) then I have to fill column (B) with the days name, and fill column (C) with the starting date from beginning till the end date.
i.e.:
E4= 24/06/2014 G4= 23/07/2014
B9= Tuesday C9= 24/06/2014
B10= Wednesday C10= 25/06/2014
I'm getting this work by a VBA code, and if it's possible to have the days names in Arabic language, and set the print area from (A1) till the (next) row after the last day in the column i.e cell (G39)
. 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 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 have a list of dates in Excel that I would like to pull the day of the week it was on: for example, cell A1 has '1/03/09' and I would like it to display in B1 what day of the week that date was.
View 3 Replies View RelatedI have a date which is recorded in the mm/dd/yyyy format, and in the adjacent column I would like to determine which week in the month this date is, from 1 to 4. So for example if the date was 07/02/2009, the week would be 1. For simplicity's sake I figued I would just have days 1-7 as week1, 8-14 as week 2, 15 - 21 as week three, and 22 on up as week 4. If there is a more accurate way of doing this, say that would be able to sart counting from the first full week of the month, then I would be very interested, but that seems unreasonable.
View 4 Replies View RelatedI have a form that users input the date (not just todays date) I want another cell to take that date and input the corresponding day of week.
example:
user inputs 03/10/09 into cell h17 I want cell A12 to automatically have the corresponding day of week (wednessday)
I am trying to create a formula that returns the date on the friday (ie last working day) of whatever week I am currently in. So, for example, I guess I would use the 'TODAY' or 'NOW' function somewhere to determine the present date - but how do I then determine the date on the Friday of the current week?
View 9 Replies View RelatedI'm using excel 2003 and I searching for a small code to automaticly generate the begin- and end- date of a week (from monday till sunday) the only variable that I wanna give is the Weeknumber. So if I write a weeknumber in cell(a,1). I want the begin-date (monday) in cell (b,1) and I want the end-date (sunday) in cell (c,1).
for example:.....
I have to test if todays day is Saturday. Don't know the syntax.
View 3 Replies View RelatedI need a macro to find the date at the end of the week.
Ie today is saturday the 20th january - Macro would return the value 21/01/07.
Or IF the date today was the 24/01/07 - Macro would return the value 28/01/07.
Is there a function in Excel we can use identify a certain date to be the week no. ?
For example, 15 Feb 08 is (part of) Week 7 of 2008
I have a "customer info column" column (b) and a "date column" column (a) is there a way that when i put a new customer into column B, that column A would automatically put the days date in which i put the new customer in column B. so b3 would have the new customers name and A3 would automatically have todays date (the day i input new customer) inserted.
so b4=new customer a4=todays date, tomorrow i input another new customer and then b5=new customer a4=tomorrows date and so on down the line.
there is no "right clickinsert date" or anything like that.
I need the following text in cell B:7 to change depending on the date.
"SAP F&R Poland Milestones for Tuesday the 9th of March 2013"
The thing is i need the date to read the date for the previous day? The reason being is that im reporting on the milestones for the previous day.
I guess im looking for a way to have the "Tuesday", "9th", "March" and "2013" to change automatically?
I have a week in sheet1. For eg.
01/07/07 in A1, 02/07/07 in B1 ....... 07/07/07 in G1.
Now in sheet2 I want to continue 08/07/07 in A1 ..... 14/07/07 in G1 and so on and so forth in following worksheets.
How can I automatically obtain the dates in corresponding columns in the following worksheets?
I can input the date and time automatically in the worksheet while a enter a number in the 3 column but when ever i enter number in the next cell whole time is changing in all the cell and it showing the same time for every number in the entering and not different time in the columns
View 9 Replies View Related