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
ADVERTISEMENT
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
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
View Related
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
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
Jun 4, 2009
I'm new here. I've searched these forums in the past for help, but never had to post, i've always been able to find something by searching. However, this time I couldn't find anything that addressed my specific need so I figured it'd be worth a shot.
Alright, I have two sets of dates, and i'm trying to set up a formular that calculates the numbers of days from the first date range that overlap/fall within the second date range.
For example:
In A3 type "1/1/2006"
In B3 type "1/1/2007"
In C1 type "6/30/2005"
In C2 type "6/30/2006"
Now, in C3 i'd like to insert a forumla that tells me how many days in the date range of 1/1/2006-1/1/2007 fall in the date range of 6/30/2005-6/30/2006. Basically for this is should be half the year, however many days that is.
Is this possible with a formula?
View 10 Replies
View Related
Mar 7, 2014
Tried with index & Match formula but it throws errors in few cases becoz Cell value are either stored in general or H:mm formats
For ex.
Cell value "05:07" has a custom format applied (hh:mm), and in the formula bar it is displayed as "05:07:00" which is not what we want. When I change the format to 'General' Excel converts the 'time' to decimal, eg "0.213194444444444".
When I manually enter 05:07 the problem is nearly resolved as it changes to 'general' without turning to decimal, but displays as 05:07:00. The formula converts it to decimal where it throws error.
View 2 Replies
View Related
Apr 15, 2012
We have started a new venture from 5th April & I need to create a template for a year and assign week no against the dates. The week1 will start from 5th April & week 52 will end on 31st March.
Also there will be a summary of the Week No(Starting Date & End Date) against each week at the top of the template. I could put the formula for the summary part but I am unable to figure out how to assign week no against the dates as given below. It is very difficult to assign the week no manually for the whole year & also it is vulnerable to error. a formula across B7:B23 which can deliver the desired result?
Sheet2 ABC1Week NoStart DateEnd Date2Week15-Apr8-Apr3Week29-Apr15-Apr4Week316-Apr22-Apr5 6DateDesired
Result 75-AprWeek1 86-AprWeek1 97-AprWeek1 108-AprWeek1 119-AprWeek2 1210-AprWeek2 1311-AprWeek2 1412-
AprWeek2 1513-AprWeek2 1614-AprWeek2 1715-AprWeek2 1816-AprWeek3 1917-AprWeek3 2018-AprWeek3 2119-
[Code] .........
View 9 Replies
View Related
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
Sep 28, 2011
Any example of counting the # weeks/days between two dates?
View 1 Replies
View Related
Sep 11, 2013
So I have this sheet template to copy for various other sheets, and on A3-A1000, they contain dates from August 1st 2013 to whenever. Starting with A3 as August 1st. The problem is now, it's September 10th, and I would like A3 to be a week before September 10th. Then A4 would be September 11th. And so on and so forth. The dates also must only consist of weekdays and the dates must be hard coded. Is this possible? I have the simple code for copying over the spreadsheet, but I don't know the rest.
Code:
Sub Submit()
Sheets("Client Name").Copy after:=Sheets(Sheets.Count)
ActiveSheet.name = Sheets("Control").Range("C19").Value
View 1 Replies
View Related
Aug 21, 2014
I have six columns.
Column A: dates
Column B: data with relates to A
Column C: dates
Column D: data with relates to C
Column E: dates
Column F: data with relates to E
View 1 Replies
View Related
Dec 14, 2007
I'm working with data as displayed below. My objective is to create a table by Model with summed quantitiies in two columns representing On-Time and Late. On-Time is achieved when Date Shipped is on or before the Promised Date. I have included the required format to be achieved in the second example. I'm willing to work with macros or VBA for the solution. I just need guidance to learn how to accomplish this task.
CREATE TABLES LIKE BELOW?
----B---- -----C------ --D-- ----E-----
6 Promised Date Shipped Inspection
7 Date Mod #
8 11/30/07 11/01/07 780 15216
9 10/10/07 11/01/07 230 15174
10 11/20/07 11/07/07 665 16314
11 9/13/07 11/07/07 230 15008
12 12/10/07 11/08/07 780 14452
13 11/15/07 11/08/07 780 15233
14 12/18/07 11/12/07 665 15219
15 12/3/07 11/12/07 780 15224
16 10/14/07 11/12/07 230 15011
CREATE TABLES LIKE BELOW?
-N-- ---O--- -P--
7 Mod On Time Late
8 225
9 230
10 335
11 555
12 665
13 780
14 1120
View 2 Replies
View Related
Dec 10, 2013
I am trying to find a formula that will return the number of week days between two dates. My specific situation is that my job sets up work orders (WO) to be completed by our staff. We have 3 dates - the date the WO was created, the date the WO is due to be completed, and the date the WO was actually completed.
I would like to subtract the Complete date from the Due date. Generally, this should always equal zero because our staff should be completing WOs on the due date! But obviously that doesn't always happen. There are times that they complete them late, and times they complete them early (yay!).
The problem with NETWORKDAYS is that even when they are completed on time, the result is 1. This formula counts instead of subtracts. I adjusted the formula to =NETWORKDAYS(A3,A4)-1 which works fine for those WOs completed on time or completed late. But for those completed early, it adds (or subtracts, really) 2 days. So for a WO completed a day early, instead of it showing -1, it shows -3. I've attached an example of WOs and the NETWORKDAYS formula I've used so you can see.
Subtract Days.xls
I'm really looking for something that will subtract week days, not count them.
View 3 Replies
View Related
Oct 14, 2011
I've managed to write a random number generator that produces multiple sets with no repeating numbers per a set.
Now, to step it up a notch, I'm trying to create an even distribution of the numbers generated which is where I could use a hand. Here's my current code:
Code:
Option Base 1
Sub RandomNumberStrings()
Dim rndno As String, strg As String, msg1 As String
Dim r1() As String, r2() As String
[Code]....
Equaling 500 numbers generated as intended, but my target is to get every number with an equal frequency.
View 4 Replies
View Related
Aug 5, 2008
I need a formula that will allow me to put a date in cell a2, and in cell a3 put the the number of days between 2 dates. Example.. For example (A2) shows 08/06/08, a3 to show the number of days from 08/06/08 to 08/14/2008--(a3 )14 and (a4) to shows the number of days from 08/06/08-10/05/2008 ---(a4) 60 days.
View 5 Replies
View Related
Feb 7, 2007
is there a way to calculate the number of days between two dates using the excel cells
example.....how do i put in formulas so excel will calulate the number of days between May 3rd and september 19?
i want to enter may 3 in say a1 then sept 19 in a2 a3 should says days between em
View 3 Replies
View Related
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
Feb 11, 2013
Within a user entered range of two dates, I would like to identify the individual calendar date(s) and count the number of Mondays which fall within the specified date range.I will eventually be using the same "Monday" code to find the same data for every day of the week within the dates ranges, but I figured I'd start with Mondays and build from there.
For Example: Date range 1/1/2013 - 1/15/2013 (date ranges could potentially encompass a full business quarter) Within the range, list each of the dates as dates. (used for comparative counting purposes elsewhere in the document)Count the number of Mons, Tues, Weds, Thurs, Fris, and Sats within the date range.Based on the example date ranges above; Mons = 2, Tues through Sats = 3 each.
View 2 Replies
View Related
Mar 9, 2009
I am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")
The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.
View 2 Replies
View Related
Jan 1, 1970
you must have the Analysis TookPack installed to use this function. It's comes with Excel. Look under Tools>Add Ins.
View 9 Replies
View Related
Dec 31, 2013
Calculating Number of Business Days Between Two Dates?
View 9 Replies
View Related
Apr 19, 2006
I have a spreadsheet where I have an intial date of issue of a warrant. Once this warrant has been executed, then a further column is populated with the date. The data I need to work out (Preferably without using macros if possible) is, if there is no execution date the length of time from issue to todays date, but if there is an execution date then length of time from issue to execution. I can do either on their own, but i would like to have only one column showing this info and if possible to update each time the sheet is opened
View 6 Replies
View Related
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
Jun 22, 2012
Is there a simple function for calculating the number of months between 2 dates.
Eg 1/5/2012 - 8/9/2020
View 1 Replies
View Related
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
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
Oct 9, 2007
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.
So 08/10/07 becomes WK40-07
I can't see how to do it!
View 5 Replies
View Related
Jan 8, 2009
just wondering if its possible to format a cell to display date and week number and if so how to go about it
eg
04/01/09 week 1
View 7 Replies
View Related
Nov 12, 2009
I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.
I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.
View 3 Replies
View Related