Tracker
HOME    TRACKER    Excel

# Calculate Absence In A Period

## how i would calculate the number of sickness days and occaisions in the previous 26 week period from todays date. I've attached the spreadsheet. So in C4 i want to have the total number of times(occasions) the person has been absent in the previous 26 weeks. and in E4 i want to have the Total number of Days (taken from E7:E20) the person has been sick in the past 26 weeks

Related Forum Messages:
Calculate A Pay Back Period For A Certain Investment
I need to calculate a pay back period for a certain investment. Excel has options for NPV and IRR but not for the Pay back period. (PBP). I've tried finding a solution using google discussion groups and some books but nothing helped me out. attached you'll find an example of my sheet. I need the PBP to be calculated in e.g. 5,3 years. but when the figures change it should automaticly recalculate the PBP.

Timesheet: Calculate Time Within A Period When Crossing Midnight
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight).

There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm.

I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. But it doesn't work when the shift goes over two days.

I have attached a spreadsheet to help show the problem .......

Using SUMIF To Calculate SUM Over 30, 90, 365 Day Period
I have a logbook which already uses a number of SUMIF formulas but have been trying to create one so that totals the number of hours flown in the above mentioned periods. I am attempting to copy the formula and then modify for the relevant column in this case: =SUM(IF(FlightDates<(TODAY()-90), 0, FlightLength))

FlightDates is the named range of the calender date in column A and FlightLength is the named range for total flying in column Z. However even though this forumla works in another situation e.g. tracking number of landings in the last 90 days, it comes up with an error.

Correlation Trends: Calculate The Trend Of Correlations Over A 4 Year Period
Column A is the COB Date Column B through N are various stock data. I would like to calculate the trend of correlations over a 4 year period. I can't seem to figure out how to manipulate the data analysis tool pak to do this.

Calculate Future Dates From Start Date With Varying Time Period/cycle
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.

For example:

I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.

I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.

I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).

Absence Calculator
im trying to put together a system on worksheets that checks 'Absence' in a rolling 12 month period. The 12 month period is any 12 months and not a financial period (eg 25/12/06 -25/12/07).

I have 36 employees and want to have their names in each sheet, calander dates across the top, will mark either a 'S' for sick or 'L' for late ect against the dates if not at work.

Once an absence has been entered, on the sheet somewhere it will show how many days that person has been off (eg, 10th Oct, 16 Nov and 22 Dec would = 3 Days absence).

How do I set up the sheets to work out how many days each person has had off in a rolling 12 month period (so that it does not calculate beyond the 12 months).

I have looked on here to see if there are any programs, formulas which may work but some have lost me in my tracks.

Formula: Absence Detail Shown
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=\$C\$11),(AbsenceDetail))

My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)

Chart Over A 24 Hour Period
I'm attempting to chart 3 series over a 24 hour period (8am-8am). The 3 series are captured in 1 minute intervals. My X axis intervals is displayed hourly though. My issue is, charting goes bad at 00:00:00. i.e. it stops.

Here are the values I have on my X axis

min: .33333
max: 1.35
maj: .04167
min: .00347
cross at: .33333

Any ideas how I can get from 8am to 8am?

Percentage Increase In A Period
I'm doing research with a professor at the University of Florida. This is my first time using Excel to any extent, so I need everything from the ground up!

I have the housing values for each of the 50 states from 1975 to 2006. I need to analyze each state individually to see if there were any points during that 26 year span when housing values increased by more than 20%. I need to know when this increase started and when values peaked and began to fall. I could do this manually, but I'm assuming that would take a while.

Is there a simple way to do this in excel? A macro, or a UDF (I have no clue about either of them by the way).

The 50 states are listed in a column and the rows next to each state having housing values for the 26 years. ( I don't know if that is relevant).

Calculating If A Period Is During A Workday
I have two dates in this format:

start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00

i need to check if the period in between those is during a workday.

our workday starts at 10:00:00 and finishes at 19:00:00

so my expected result is 3 hours. (1400-1100)

other example:

start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00

result is 1900-1100 + 1100-1000

= 9 hours

because it is during two workdays...

is there a way to calculate this in VBA?

Sharing The Value Of Job Over A Certain Period
i need to share the valve of the job over the months. see attahed excel sheet. say the job worth 10k has a start date of the 30th of nov and the end date is the 1st of dec. the value of the job is shared between two days and then put into the relevent cell. as regards to the months they are not calander months so the start date and end date of the months are diffrent see attached sheet for full details.

Period Replaced By Colon
Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.

Time Trial Period In My Spreadsheet
I've searched a few of the posts here about freebies in terms of adding a trial period to your spreadsheet. I'm just starting to get into VBA programming (beginnner), but I was wondering if anyone here could suggest actual programs or add-ins that I could buy that would allow me to create a trial version of my spreadsheet? either by allowing the user to use it for a few days or to allow the user to use it for a specifed # of times.

I'm willing to pay for this, but I can't afford those expensive Licensing/Copy protection programs....I need a simple solution in between \$20 and \$60....

Calculating Time Across A 24 Hour Period
Calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.

Example

In B5 Startime is 22:00
In C5 Finishtime is 06:30

In I3 increment begins at 00:00
In I4 increment ends at 00:30

The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.

Work Out The Value Of Cars Over A Period Of Time
I have a project where I have to work out the value of cars over a period of time.

The cars depreciate at 36.9% per year over a 5 year period and I can't work out for the life of me how to create a formula in excel that give me the values at the end of each year.

Each car costs £10000 and I need to know what the value of each car is at the end of each year

Converting Time Period In Hours
How do I convert 7.30 hours into 7.21 (ie 7 hours 21min.) Note I do not wish
to use the standard hour:minute formatting.

Graphing - Selecting Specified Time Period
I have sheet that is populated with data daily. I wish to create graphs from information contained within a table in this sheet where i can select the start date and end date of the graph possibly from a drop down menu. I will be creating multiple graphs and wish for them all to change when i select the time period.

Sumif If The Column Is In The Correct Period
I have a set of costs in column c (more that shown below) that have an indicator - for example 0 or 30 (there are more).

I need to populate rows 18 and 19 with the sumif - i have other tables where it is a straighforward sumif as the periods align, here though I need to check that the periods (rows 4 & 16) are checked and then the right data entered underneath.

I can't find a way to combine the sumif, with an if statement checking that the period is correct too? ...

Read Data Only To Current Period
I'm trying to total data only up to the current period so I presume the answer lies in a =sum and index but I can't get it to work. The attached worksheet explains it better.

YTD And Period Sales Report
I have an Access DB that I query with excel and I pull two years worth of sales data. I have tried using a pivot table report to display the following data, but I can't figure out how to display the data in the following format.

The pivot table will give period and YTD but the totals for YTD are not cumulative for the year up to that period (it seems to total the period only).

For the current Year- period (month) and YTD (only up to the period displayed).

For the last year- period and YTD (only up to the period displayed).

The fields I query are Customer, City, Product, Salesperson, Period(month), Year and Sales

I have tried putting the queried data on one sheet and then using formulas on another but I am not having any luck.

I would also like to be able to select which period I am viewing but this is secondary.

I can upload an example if necessary.

Report On Date Period And Subtotal
I have attached a sample of my spreadsheet to use as a reference. I suggest you open that up and give it a look through to get an idea of what's going on.

What I'm looking for is a way to create a report (the quickest and easiest way of course, as always) which will give the total for the columns 'Exit Weight', 'Net Weight' and 'Entry Weight' sorted by each company between the periods of the 25th of each month to the 24th of the next month (the Dates are in Column A obviously)

Below is an example of the output I am looking for:

Company: Period: Exit Weight: Net Weight: Entry Weight:
EASTWELL HA.. 25/10/07-24/11/07 102.2 124.3 200.14
25/11/07-24/12/07 143.7 185.2 250.18
25/12/07-24/1/08 ... ... ...
BOURKE TRANS.. 25/10/07-.... ... ... ...

the totals for the Exit Weight, Net Weight and Entry Weight are not the actual totals, they were just put in to give you an idea of what I'm talking about.

And on and on. If there is a way to automate this with Excel VBA and some sort of code that you could give me to plug into my spreadsheet I would be very appreciative. I understand this is a bit complicated and would probably require a few steps so if don't get the answer I'm looking for I'll probably find a way to split it into steps and then ask the questions seperately.

Finding The Quarter Period Of A Date
I'm trying to find out the date quarter of a given date in cell A1.ie Q1,Q2 etc.
I've found the code below to work out fiscal quarters.I thought if I dropped the +1 from the end of the formula it would work out the quarter in non fiscal terms. This however only works from months Jan to Nov. December returns a zero.

=MOD(INT(MONTH(A1)/4)+1,4)+1

How To Get The (average Or Sum) In A Limited Period Of Time
This code help me in typing the date and time in any cell of column (B) automatically just in case I enter somthing in any adjacent cell of column (A)

Automatically Close UserForm After Time Period
I have the following code in a simple userform containing only a label:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub UserForm_Activate()
DoEvents
Sleep 4000
End Sub

When I invoke this as a single macro in a blank workbook and immediately change focus by pressing ALT+TAB to enter NotePad the userform is shown and exits normally after 4 seconds.

However, when I issue this code from within a larger macro, and hit ALT+TAB to Notepad, the excel application hangs on the displayed userform until I click back into Excel.

If required, I can happily send you the code as an attachment.

Completing Probation Period This Week From Join_Date
I have got Names in column A and Join_Date in column B. In column C I want those whose Probation period is going to finish this week. Probation is of 90 days.

Calculating The Number Of Business Days In A Specified Period
It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g

Mondays, Wednesdays and Fridays of June
June 1, 2006
June 3, 2006
June 6, 2006

Tuesdays, Thursdays and Saturdays of June
June 2, 2006
June 5, 2006
June 7, 2006

This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months

Check If Final Week Of Fiscal Period
I am attempting to check to see if the current week is the end of a period or not. If it is, I need to display some text, and if it is not, I need to display nothing.

Since I am Computer Science major, the best way I can describe this is through and if structure from a low-level programming lanuage (like C++ or Java):

Removing Chars Not (a-z,1-9,space,comma,period)
I have a file of names that has some undisplayable characters. I am trying to match against a different file. It works if I overtype the "blank" fields with a space. How can remove these trailing problem characters whatever they are? I tried CLEAN and TRIM and the mystery characters are still there.

Sumproduct: Count Number Of Leaves Within A Specified Period
=SUMPRODUCT(SLVL!\$B\$6:\$B\$49>=\$A\$2)*(SLVL!\$B\$6:\$B\$49<=\$B\$2)*(SLVL!\$A\$6:\$A\$49=A12)

I'm sure that there had been an error somewhere but I can't figure it out. I'm getting zero value. Situation: need to count number of leaves within a specified period, thus I opted to use Sumproduct.

Determining Number Of Specific Days In Period
I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.

Chart A Graph Of Jobs Over A Period Of Time
I have an Access database, with a table of jobs processed.

One column for Job ID (Unique), Date it was processed, By Who it was processed and Comments ascoiated.

I need to chart a graph of Jobs over a period of time. How many jobs done per date. In excel preferebly, I have programs like Database Plus for excel. And I am familiar with VBA. I can't figure out how to uniquely sort how many jobs for each date. As there are numerous job entries for on the same dates. I think it can be done with an SQL query or sorting it with VBA somehow. Any help will be greatly appreciated. Or can anyone suggest an application designed for charting with Access databases in Excel?

Title Of A Document Contains A Period And Is Stopping The Code
I have a two documents whose names contain periods out of 70. Example is St._Paul and St._Louis_Park. When I run through the code it stops it with the error that the name cannot be found. My list looks like this..

Club Name
Eagan
Apple_Valley
St._Paul
etc.

When I remove the period it works. Is there a way to make it work with the period in there so I don't have to change a bunch of files and documents? Here is the code...

Sub OpenUp()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks(ThisWorkbook.Name)
Set ws = wb.Worksheets(1)
With ws
TotalRow = .Cells(1, 255).End(xlDown).Row
Finalrow = .Cells(TotalRow, 1).End(xlUp).Row
End With
For i = 2 To Finalrow
clb = ws.Cells(i, 2)
psw = ws.Cells(i, 3)
psw2 = ws.Cells(i, 4)
mnth = ws.Cells(i, 5).........................

Extract Text Before/After Period (Full Stop)
I have a series of text - domains actually (i.e. goldintre.cr.usgs.gov.) - and I need to extract the text that appears before the first period in one cell, and the text that appears after the first period. Both have to be without the periods. So in the above example the result should be 'goldintre' in one cell and 'cs.usgs.gov' on the other.

Display Employees List Whose Passport Going To Expire In Given Period
I have my Employees List in one Sheet and in another Sheet2 I want to Display those Employees Names and List whose Passport going to Expire in the dates entered in Sheet2 A1 and B2 Cell.

Payroll Extract To Work Only On Certain Pay Period Dates.
I currently have an Excel payroll extract that populates a start date and end date via the calender control 11 user form.

What I have found out is I need to inject some sanity to this application. I can not have users select days that are outside of a pay period.

The users should only be able to select the first or the 16th of the month for a pay period start. Then they can only select the 15th or the 28th/31st for the pay period end.

What I am trying to do is have them select the month and the pay period start date, then the end date would automaticly be selected. But I don't want to have to create a bunch of loops to counter for the differing month end dates or leap years.

Automatically Close If A User Is Inactive For A Period Of Time.
We have one shared excel workbook and it is used by many people (more than 20 simultaneously), is there any way to auto save & close the workbook (session) if a user is inactive for specified time.

Pivot Tables To Find Max Value In Time Period
I have a in field column M of my table that gives the week number of various dates within the table.

I would like to find the high value reached during each week based on the Highs which are located in column P, the highs consist if workdays during each week number.

I would like this to appear as a new calculated field entitled "Week High", I am assuming I need to input a custom formula to do this. I also want to create calculated fields for the the high reached during each year, the year is in column L

Stop Macro Running After Specfied Time Period
I'm opening a workbook and then running the macro in the workbook. The problem is I would like to build a "timeout" feature if the macro is running too long (as some of the macro's this will run can take days to complete) but I don't know how to run the macro asynchronously. Also I need to know if i can get it to run asynchronous is there an event that will tell me when the process is finished. The calling application of the macro is written in VB6 and opening excel workbooks to run the macros out of. also the "timeout" feature must be done from the VB6 application it cannot be edited into the the workbooks containing the macro.

Accumulate The Hours For An Invoice Period And Job Code Combination
My problem is that I have a worksheet tab (RawTimeSheetData) which contains a whole series of week/timecode values for a range of people.

I want to accumulate the hours for an invoice period / job code combination. As an example in the tab InvoicePeriodSummaryTimes cell D6 i want to sum all the hours from RawTimeSheetData where both cells A6 & B6 from InvoicePeriod tab = cells D6 & E6 from the rawdata tab.

Sumproduct Formula: Multiply Monthly Values With A Maximum Value In Any One Period
Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.

Correct Way To Reference A Question Mark And A Period (Apostrophes And Quotation Marks)

I know the following is a correct way to reference a question mark and a period....

Multiple SUMIFS (find And Add Up All The Total Requests For Only The Summer Period (Column Q))
I have a list of about 2,500 entries containing information of all my works leave requests for 2009. This list contains more than one entry per employee, as it shows all their individual requests for different weeks. The list is compiled pulling through data from other worksheets. It looks like this...

Sheet Name: MASTER

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column E = Week ending
Column F to L = Days of the week
Column M to O = Request Type
Column P = Number of days requested for the week

I then have another sheet (Called REQUESTS) with all the employee names (1 entry for each employee), ID numbers and total requests for the year. This looks like this...

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column P = Number of days requested for the year
Column Q = Number of days requested for the summer

I know how to locate and add up all the requests for each individual for the whole year (Column P) from the MASTER sheet to the REQUESTS sheet by using the flowing formula for each employee.

=SUMIF(MASTER!\$D:\$D,D2,MASTER!\$P:\$P)

But I don't know how to ask Excel to find and add up all the total requests for only the summer period (Column Q). What I want Excel to do is this................

Incorrect Sumproduct Array: Sum Up The Total Number Of Hours Consumed On A Particular Activity Within A Specified Period
I am trying to sum up the total number of hours consumed on a particular activity within a specified period.

=SUMPRODUCT((\$C38:\$C176>=\$B7)*(\$C38:\$C176<=\$C7)*(\$B38:\$B176=\$D5))

Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.

Amortization Schedule: Auto Update Based On Loan Period & Number Of Payments Per Year
I have uploaded a sample amortization schedule.

1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.

2. Also, if a value is entered in column E, then i require the whole table to update as well.

Automatically Change Cell (With Time) Color After Time Period Has Passed
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is:

i) check whether they are in an input cell
ii) if so, then prompt the user with the 'Font Color' dialog box
iii) apply the font color selected to the input cell

I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color

Count Number Of Full Time Periods Within A Time Period
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)

I also have numerous set time periods that i'm interested in.. (about a dozen or so)

for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00

What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.

So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2

and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.

and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under \$25K, recognize in next month (month N+ 1)
- \$25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over \$100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

Sub Calculate
I am trying to call a sub calculate but I keep getting errors, is calculate a reserved sub name?

How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..

-- got this numbers 1--2--3--4--5--6
-- want to calculate how much combinations of ( 2 ) numbers possible
-- want to calculate how much of ( 3 ) numbers possible
-- want to calculate how much of ( 4 ) numbers possible
-- want to calculate how much of ( 5) numbers possible

-- never 2 same numbers together (2-2) or (2-2-3) or (2-2-3-4) may not be in list

-- How can i make the result visible in kind of list ?

I would be nice if somebody knows a good solution..

Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10.

Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this