Calculating The Date At The End Of Each Week

Apr 24, 2007

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


ADVERTISEMENT

Calculating Number Late Last Week From Multiple Dates / Date Sets

Jul 2, 2014

I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.

The problem I am having is calculating the number of late jobs that existed during the previous week.

The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use

=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.

I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.

My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.

Movement Log.JPG

Status Chart.JPG

View 1 Replies View Related

Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets

Jan 19, 2010

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"......................................

View 9 Replies View Related

Calculating Two Values Between Two Dates (per Week)

Jun 9, 2014

I need to make a table for an injury category per shift per week. (Falls per shift per week)

I have attached an example of the spreadsheet. I have a formula in the table now that was calculating just the injury type per week but just need to add the function to read per shift but can't seem to get it to read correctly.

View 2 Replies View Related

Calculating Week Number Without WEEKNUM

Oct 14, 2008

We have a fiscal calendar which starts Oct 1. I would like to display the proper week numbers. I worked out a formula which seems to work (except for week 53) but it would be better if I didn't have to rely on other users having the Analysis Toolpak installed. My date is located in '3930!I4' and this is the formula that works with the toolpak: ...

View 9 Replies View Related

Calculating Average Number Of Staff Per Week

Aug 4, 2014

I want a formula to calculate the average number of staff working per week in the attached data.

View 9 Replies View Related

Formatting Date: Add The Month And The Text "Week" Before The Week Number

Nov 24, 2009

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.

View 3 Replies View Related

Create Macro That Filter Date In A Column From One Week Previous Till Current Date

Mar 14, 2014

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

View 8 Replies View Related

Show Week Number Of Today Date From Starting Date

Jun 27, 2014

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 Related

Find Pay Date From Date Sold (Friday Of Following Week)

Feb 17, 2014

If 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 Related

Week Number For Date Relative To Date Range

Mar 17, 2008

Is 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 Related

Convert Date To Year/week Of Year/day Of Week

Apr 6, 2007

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)

View 9 Replies View Related

Using Date Part To Get WEEK, But Then To Getthe Date

Jan 6, 2010

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?

View 9 Replies View Related

Date And Day Of Week

Apr 11, 2007

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 Related

Automatic Week And Date

May 22, 2014

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 Replies View Related

Getting Day Of Week From Date Cell?

Jul 9, 2014

. 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!

View 1 Replies View Related

Getting The Date For The Start Of The Week

Jun 4, 2008

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.

View 14 Replies View Related

(2003) Get Day Of The Week From A Date

Jul 31, 2009

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 Related

Convert Date To Week

Jul 24, 2009

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 Related

Convert Date Into Day Of Week

Mar 11, 2009

I 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)

View 5 Replies View Related

Week Number Of Date

Sep 5, 2006

I'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:.....

View 4 Replies View Related

Testing Date For The Week Day

Oct 21, 2006

I have to test if todays day is Saturday. Don't know the syntax.

View 3 Replies View Related

Find The Last Date Of The Week

Jan 20, 2007

I 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.

View 9 Replies View Related

Get Week Number From Date

Feb 15, 2008

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

View 8 Replies View Related

Find Week Based On Date

Apr 17, 2014

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‎

View 12 Replies View Related

Formula To Get Date From Month And / Or Week

May 30, 2014

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).

View 13 Replies View Related

Automatic Week Commence Date

May 26, 2009

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?

View 11 Replies View Related

Sum Of Week-to-date If Meeting Criteria

Nov 6, 2009

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

Date Lookup To Get Week Back

Nov 25, 2009

My vlookup fonction fell in a black hole after the november 23rd 2009.

What would you reckon?

View 14 Replies View Related

How To Make Date Show Day Of The Week

Mar 3, 2014

If I have a date showing 1-Feb, how can I make it show the day of the week?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved