Capture And Calculate Data Based On Work Week In VBA Or Formulas

Jul 21, 2014

I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.

Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.

For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.

here is the distribution of columns per week as reference.

(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns

(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns

View 1 Replies


ADVERTISEMENT

Calculate Work Hour Phone Time By Day, Week & Month

Aug 21, 2008

I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.

View 2 Replies View Related

Calculate Day Of Week Based On Listed Day?

Aug 14, 2012

I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.

Excel File as follows:

Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)

I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.

View 3 Replies View Related

Calculate Friday Based On Week Number

Jan 9, 2012

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)

View 8 Replies View Related

How To Calculate The Week Number Of The Month Based On A Date

Dec 30, 2013

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.

View 4 Replies View Related

Calculate Time Elapsed And Compute Day Of Week Based On Date

May 27, 2014

1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)

2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F

2=S-S

3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)

2=PM (15-2259)

3= MN (23-659)

View 9 Replies View Related

Calculate Formulas Based On Top 20%

Jun 27, 2008

I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.

At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.

EXAMPLE:

TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00

Employees - 4
SUM - £4,237.86
Median - £1,033.93............................

View 14 Replies View Related

Using Lists Function Calculate The Outstanding Work Estimation Based On Available Information

Feb 10, 2009

I am trying to create a worksheet which can calculate the Outstanding Work Estimation based on available information.

The information available with me is presented in Rows 3, 4, 5, and 6. For these types of work orders, I have information regarding their Previous Outstanding Status, New Orders, Completed Orders, and at presented Unworked Orders (or Outstanding orders at the end of Jan 2008).

Second set of information is available in rows 9 to 23. This information provides for which type of orders were completed by Staff 1 and Staff 2.

Based on these two sets of information, I am trying to get to the point where I can calculate the following:

a) Applying the weight of orders (namely 1A, 1B, 1C and Blend) by using the drop down list function in column B, I should be able to get the results in Column C (C25 onwards till C40), Column D (D25 onwards till D40), and Column E (E25 onwards till E40). Simlarly for all other months...for February, March, and so on...till December 08.

View 2 Replies View Related

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

Mar 3, 2009

In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....

I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.

From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.

View 9 Replies View Related

Formula To Calculate Number Of Work Weeks The Data Has Been Collected?

Dec 27, 2013

I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.

View 3 Replies View Related

Set Up A 6 Day Work Week

Aug 10, 2006

I normally use the WORKDAY function in order to create an involved scheduling
program. However, I have no idea how to create a formula that would count
Saturdays as a workday. I usually type in one date and then the dates kick
out to an end date several months later. How do I create a formula to
include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06,
A3=8/15/06,etc...)?

View 9 Replies View Related

Calculate Pay For Shift Work With Different Rates Based On Shift Hours

Apr 11, 2008

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

View 8 Replies View Related

Subtract Two Dates To Get Number Of Week Days - Networkdays Does Not Work

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

Count Formula- Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week

Apr 9, 2009

I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.

As a side question, will count work for a range of data or only one column or row at a time?

View 9 Replies View Related

Pull Data Based On Day Of Week From Another Workbook For Previous Three Weeks

Feb 4, 2013

Here is what I have and am trying to do:

I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):

Date:January 01, 2013
Day:Tuesday

I then entered all the data needed below that (B5:E26):

TimeTransactionsTotal SalesAverage Sales
12:0039 $352.97 $9.05
13:0053 $416.98 $7.87
14:0044 $339.42 $7.71
15:0043 $304.96 $7.09
etc.....

In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).

View 1 Replies View Related

Calculate Sum By Week

Sep 6, 2007

I have a series of data in different columns, one of them is the date (daily, for like 5 years, exept weekends and national holidays), but I want to make another table with weekly data (which should be the sum of the daily data in a week, for each column). I thought there might exist any combination of formulas for that, to put once and drag it, and maybe it's pretty obvious, but even though I thought about it a lot, I couldn't find the solution.

View 6 Replies View Related

How To Capture Data From One Cell To Another Based On Another Cell Value

Apr 18, 2014

i am in need of the following.

A B C

9.30am 120 120
9.31am 119.9
9.32am 119.8
9.33am 121
9.34am 120.9
9.35am 120.8 120.8

Here A and B value is keep on changing from external source, when A1=9.30am, the B1 value 120, then C1 should capture the B1 value and stored in it. like wise it captured and stored for every 5 minit or as per requirement.

View 9 Replies View Related

Rota - Calculate The Hours Every One Has Done Each Week

Nov 19, 2007

way to calculate the hours every one has done each week to make sure everyone has done the correct hours. I was about to start using a calculator and then realised it would take forever.

I've included a zip file of the excel file

View 14 Replies View Related

Calculate The Cumulative Sum Without Inserting Another Row In The Week

Sep 30, 2008

I have two rows one with the nr of weeks and another with revenues like that:....

i need to calculate the cumulative sum without inserting another row in the week that i define like an input so it could be changed.. for example the *** sum in the week 5 shloud be 900.

View 3 Replies View Related

Calculate Days In A (06 Day)Working Week

Dec 27, 2008

to calculate no.s of workdays between two given dates,

1.We work 06 days a week (so only Sunday is OFF)

2.What if I have some holiday between the given dates + Sunday OFF

View 9 Replies View Related

How To Calculate Date Fall In Which Week

Jul 28, 2009

Need a solution for calcuate a week in user enterd date?

Example

A1 A2
07/01/2009 1
07/01/2009 5

View 9 Replies View Related

Calculate Week Number From Sheetname

Oct 18, 2006

Daily i import sheets into excel and the sheet name is uniformed to the following

20061017_BNKREC - 20061018_BNKREC - 20061019_BNKREC ..........

just for clarity purposes

[2006] = year, [10] = month, [17] = previous day, [_BNKREC] = report type

I'll be creating a graph to which shows account balance by week, by account.
The data will be coming in daily. i know i will need to create either a dynamic range or copy my data into a new sheet. My head is spinning because i need excel to somehow (either in a formula or VB) determine what WorkingWeek the sheet is in. I dont want to have to keep adjusting formulae or ranges when import a new sheet..

bare with me here as its hard to explain ................

View 2 Replies View Related

Formulas Won't Calculate. Just Displaying As Formulas

Dec 11, 2008

I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.

View 4 Replies View Related

Excel 2003 :: Calculate Formula For Third Value Of Week?

Nov 9, 2012

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.

View 9 Replies View Related

Calculate Total Hours Per Week Per Schedule

Dec 14, 2013

Please refer to attached sheet.

I am using the attached to schedule the employees. All I want is a formula to add up all schedule hours per week per schedule in cell R2,R3 and R4.

Book2.xlsx‎

View 2 Replies View Related

Calculate Overtime From Daily Hours For A Whole Week

Feb 13, 2014

calculate overtime hours from daily time entries.
Normal hours are 7.6 per day
Time 1/2 is hours over 7.6 but no more than 2 hours
Double Time is all hours over that.

I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.

WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068

View 5 Replies View Related

Calculate Week Number And Call Correct Sub

Jul 19, 2014

I have two processes that has to run depending of the week number of the year. So even weeks has to run "sub 2" and odd weeks has to run "sub 1".

Weeks with number 1,3,5,7,9,11,13,15 etc has to call "sub 1" and weeks 2,4,6,8,10,12,14,16 etc has to call "sub 2".

The filenames consist of "name_name_YYYYMMDD.xls" (example: SteveSmith_Runningshoes_20140719.xls)

So vba needs to calculate the week number by reading the YYYYMMDD of the filename and then call the correct sub.

View 6 Replies View Related

Embed Formula In VBA (calculate The Absolute Week )

Dec 28, 2009

I need to calculate the absolute week and have found a formula to do so. Is there a way to embed/execute this in VBA?

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

View 4 Replies View Related

Calculate Number Of Week And Days Between Two Dates?

Sep 28, 2011

Any example of counting the # weeks/days between two dates?

View 1 Replies View Related

How To Calculate Week Number That A Date Falls In

Aug 6, 2013

Of the 52 weeks in a year, I want to know the week number that a date falls in, e.g. date - 05/08/2013 falls in Week No. 32. What's the formula to get this answer?

View 2 Replies View Related







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