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 RepliesI have two columns with total hours worked at different places and a grand total for both together in a third column.
I'm trying to sum the total hours for the week so far from Thursday till the following Wednesday for each week.
I want the wookbook to look up todays date and tell me what the total hours are for the current week
I'm using Excel 2003 - see the file attached
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"......................................
On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:
(value*Tvalue)+(value*Tvalue)+(value*Tvalue)+(value*Tvalue)/(Tvalue)
(this is not the actual formula but simplified, that's not really important).
It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):
I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula
(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be:
(0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).
Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective
I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.
I'm working with three large data sets covering a month of data in 5 minute intervals. There is a single date and time column which I am using some custom formats to list Time in 12 hour format, AM or PM and Day of the week.
Based upon the various analysis scenarios, adding some Autofilter columns is meeting most of my needs. One issue I am having is that I can filter on a single day using the built in list that Autofilter provides. But I have been asked to look at weekend vs weekday.
Since Excel 2003 only offers two filter criteria, I thought I could use a custom filter with Saturday and Sunday as include or exclude criteria. But, what I am findings is that while the Autofilter shows each day in the dropdown, certain attempts to use them within the custom autofilter do not work.
For example, equals Saturday and equals Sunday correctly returns only weekend data. However, if I try changing both to Does not equals, the result is that no data is filtered.Some of my research indicates that the problem is that the date information being presented as days of the week would need to be text strings . This seems to shed some light on the issue as any attempts at using wildcards fail.
Objective: I have a financial spreadsheet, which i want to bring to my front sheet the sum of data in between 2 week ranges. For example, if I select week 26 and 52 then all the relevant data will be summed into the relevant cell on my front sheet.
Current Technique: I have on my "data" spreadsheet my columns in B1 down listing Week 1-52 and then the cell headings in B2 onwards. I have created a summary table which currently shows the 4 quarters of the year (1-13, etc) and I am using helper columns to pull this data onto my "front sheet", depending what selection is made on the drop down e.g. 1-13, then all summary data will be dragged onto my front sheet.
On the front sheet, in each cell I need information to be displayed I use the following formula;
"=IF(ISERROR(INDEX(Data!$C$56:$BF$60,Data!$BI56,COLUMNS('Front Sheet'!I13:J13)))"
As you can see the quarter data is useful, but a more flexible date range would be more useful to sum data between any selected week.
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.
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 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 Relatedi 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?
Cell B1 contains a date, then B2 contains a formula that says:
=IF(A1>TODAY(),A1,A2)
A1 contains a green tick and A2 contains a red cross.
What I am trying to add is that if B1 contains no date then B2 needs to be blank.
I tried using =IF((A1="",0),(A1>TODAY(),A1,A2) to get it to show a 0 if there was no data but this doesn't work.
I am using Excel 2003.
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 RelatedIn 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 Replies View Related. 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 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'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 uploaded an example file.
Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.
The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).
So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).
Attached 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?
On the attached file, I would like the values from the Transactions worksheet to show up in the appropriate cells in the XYZ Expenses worksheet. The catch is, I would like the XYZ Expenses worksheet to only show the values from the past week. Past week could be literally, but I'm guessing that might not be possible and it would have to use the past week from the most recent transaction date - which would be fine. So if it worked right, XYZ Expenses > F7 would show -$80, which would be from Transactions H3+H6 (and not -$130, from Transactions H3+H6+H9).
View 3 Replies View Related