Calculate Days Between X

Nov 12, 2013

I have a data set that looks like the following.

Description S M T W T F S
Item 1 X X
Item 2 X X

I have a few thousand rows of this. The X's could be placed in any column. I need to go through this data and determine the maximum days between X's for each row. I can't think of how to do something like this with either an equation or with VBA.

I can't get it to line up in the table above. But basically, there could be an X in Sunday and on Wednesday in one row. The next, could be every day of the week, the next could be any other combination. There could be 7 X's, 1 X, 3 X's, whatever.

View 1 Replies


ADVERTISEMENT

Calculate End Date Using Start Date And Number Of Days Excluding Specific Days

Nov 22, 2013

I want to calculate the end date of my German courses. This is how it works:

A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.

Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.

Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.

The workday function on excel cannot do this and I do not have any programming skills to work with VBA.

Legend:
*LU = lesson units; 1 LU is 45 minutes

Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14

[Code] ...........

View 7 Replies View Related

Formula To Calculate Days?

Apr 22, 2014

I am currently using the following formula which has been working great for what we needed it to do:

=IF(ISERROR(NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1),"Not Completed",NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1)

The formula determine what was been completed on the previous work day. If it fell on a weekend or a holiday specified in another table, it would consider it the following weekday's work. However, the needs of management have changed and they would now like to see anything completed on a weekend or holiday push back to the previous weekday and I'm not exactly sure how to modify this formula to do that.

As an example, work was completed over Good Friday. Yesterday (Monday) when we ran the report, none of those showed up in our list of completed items until today (Tuesday). They would have liked those numbers to be included on Monday's report to get an accurate picture of work that was completed.

I have attached some sample data. Everything completed on the 18th, 19th is counted as 1 day behind but we would like 18th and 19th to be shown as 2 days behind, just like April 17. That way, if we ran the report today and set the filter to 1 (for column C), those items would not show up, but they would have shown up on yesterday's report.

Sample.xlsx

View 4 Replies View Related

Calculate Sick Days

Jan 27, 2010

I’ve tried formulas for this but nothing is working, and was wondering if there was some vba that would do the job

Please find attached a sample sheet of where I’m up to.

I’m trying to calculate when someone goes sick to see whether they are entitled to company sick pay (CSP) Statutory sick pay (SSP) and most people will have waiting days (W D) before qualifying for CSP.

A6:A735 have set dates for two years
D6:D735 have the sick days an employee has taken
B6:B735 Matches the sick date to the date in column A

G2 has how many days entitlement for CSP
H2 has how many waiting days (W D) before CSP can start

In Column C i need CSP, SSP, or W D next to the number 1

And if someone only has 1 day sick they would not qualify for any of the above and I just need “S” in column C.

View 14 Replies View Related

How To Calculate (first Of The Month After 60 Days)

Apr 17, 2013

I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.

Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?

View 8 Replies View Related

Calculate Working Days And Holidays?

May 13, 2014

1. Find the working days between 15th Nov 09 to 28th

2. Add a column that shows the date after a number of months from today date. The number of months from todays date for each of the country is mentioned in column No. of Months after which revised.

3. Find the date our training will end using excel considering the fact that SAT and SUN are holidays. (Use the date when your training started)

View 2 Replies View Related

Calculate Number Of Days Between Two Dates?

Jul 29, 2014

I am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.

View 2 Replies View Related

Unable To Calculate TAT Between Multiple Days?

Feb 18, 2014

I want to calculate the TAT between multiple date: For example: I have a request I received on 1/2/2014 and I sent to Abhi on 1/2/2014(Same day) and I got a reply on 1/3/2014 from Abhi. I forwarded the same request on 1/3/2014 to Prasad and got a reply on 1/5/2014. And I closed the request on 1/5/2014. How do I calculate TAT on this ? I want the Tat to be shown as 3 days because I just took three days on the request to complete that is 1/2/2014, 1/3/2014 and 1/5/2014..

View 4 Replies View Related

Calculate The Average Amount Of Days?

Mar 26, 2014

I need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.

Assuming that B1=0 and C1=1 (in the # of days row)

# of Days 0123456789101112131415Total Files
File Count011712113203000000049

View 8 Replies View Related

Calculate Days Between A String Of Dates

Nov 21, 2008

I need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.

View 4 Replies View Related

Formula To Calculate Sick Days

Jan 8, 2009

I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell.

For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.

View 5 Replies View Related

Calculate Days Between Subtotaled List

Apr 17, 2009

In the attached workbook, I have subtotaled a list of parts. I want to add a formula beside the subtotals, under Column C, "Days BO", that subtracts earliest date in column R from the latest date in column R, for each part number.

I have added a simple formula to the attached example that does what I want, but the full sheet will be thousands of lines weekly, with varying part numbers, and different counts. I don't know how to take the count function into account so I can copy a single formula all the way down the sheet.

View 4 Replies View Related

Calculate Number Of Days Between Periods

Apr 28, 2013

Our company has been taken over by new mgt (Y co) on 1st Nov 2012 from old mgt (X co). Y co has the policy of providing employees with EOS benefits as mentioned below:

1st 5 yrs - 1 month salary per year
5-10 yrs - 1.5 month salary per year
+10 yrs - 2 months salary per year

But for deciding the slab, total period of service is considered. The below table provides the calculation of days as on 31st Dec 2012 based on different joining dates which I need to fix by way of formula.

Name
Hire date
Salary
Total service (days)
X co
Y Co
1st 5 yrs
5-10 yrs
+10 yrs

George
09-02-2005
1000
2883
2822
61

61

[Code] ........

View 4 Replies View Related

Calculate Average Days Between Dates For Each ID?

Oct 23, 2013

My sheet looks like this - example:

ID Dates CombinedAverageDaysFromID
0001 2012-02-01 ?
0001 2013-08-10
0001 2013-10-10
0402 2011-01-02 ?
0402 2013-01-05
0402 2013-01-22
0003 2009-02-04 ?
0003 2009-12-04
0003 2010-01-04
0003 2010-03-03
0003 2010-08-02
0003 2012-04-04
0003 2013-01-05
0003 2013-10-03

I need to calculate the average days between the dates for each ID? How do I do this?

View 4 Replies View Related

Calculate Working Days Function

Aug 28, 2008

I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.

View 9 Replies View Related

Calculate The Number Of Days From Start To End

Sep 4, 2008

I have 2 dates a Start Date and an End Date.
I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column.

Start End Yr1 Yr2 Yr3
11/01/99 11/01/00 11/01/01
10/31/00 10/31/01 10/31/02

Col A Col B Col C Col D Col E

10/31/00 07/05/01 365 117 0

I thought a series of IF statements but I am open to an easy solution...

View 9 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

Calculate The Time Between Two Points (on Different Days)

Mar 1, 2009

I am trying to calculate the duration between two times on different days.

For example - I want to know the number of days/hours/minutes/seconds between 25th Feb 2009 20:00 & 27th Feb 2009 22:00.

I am sure that the key is how I enter the data into the cells and how those cells are formatted, I just cannot get Excel to see that the times are on different days. At the moment my data is in a raw format with times in one column and dates in another.

View 9 Replies View Related

Calculate Number Of Days Between 2 Dates

Jul 28, 2009

I have a start date formatted as dd/mm/yyyy in cell A5
I have an end date formatted as dd/mm/yyyy in cell E5

In cell F5 I would like the result of the number of days between E5-A5 (Excluding weekends)
In cell G5 I would like the total number of days in the range F5:F25
In cell H5 I would like the total number of working days from A5 to Today

I am using the following formula to get the result in F5 etc
=SUM(INT((WEEKDAY(A5-{2,3,4,5,6})+E5-A5)/7))
I have copied this down and in the blank cells I get #NUM!

In cell F? were there is no date entered in cell E? I get a minus number (which I can hide using conditional formatting), but the minus number obviously has an affect on the result in G5, as cell G5 gives me #NUM! instead of the result for the current numbers in row F

View 9 Replies View Related

Calculate Times That Span 2 Days

Jul 31, 2006

I have formated my cells as custom hh:mm and want to have a 24 hour clock calculate time.

In cell D6 I have starting time of 23:00
In cell E6 I have finish time of 07:00

This should equal 7:00 hours but I get ######### am I doing somthing wrong or do I have to format the cells differently.

The formula in F6 is E6-D6.

View 6 Replies View Related

Calculate Work Days Between 2 Dates

Sep 14, 2006

i have a few fields with dates in my excel sheet. I also have a field, where i want to automatically insert the business day between two other date-fields! Business date means days without the weekend and public holiday! Is it possible to do that? how?

View 9 Replies View Related

How To Calculate Payment Due Date From Number Of Days

Jul 7, 2013

How can i get the actual Payment due date from no. of days using macro. i. e.

Posting Date Dues Payment Date
------------ ------ ---------------

22.06.2013 21 days ???

View 9 Replies View Related

Calculate Number Of Days Between A Date And Today

May 18, 2006

I need to calculate on a spreadsheet the number of days between a specific date and the date the spread sheet is opened. I need to know the number of days a merchandise has been stored.

View 5 Replies View Related

Calculate Number Of Winter Days Between Two Dates

Dec 11, 2013

I'm looking for the formula to calculate the number of winter days between two dates.

Say, that I'm having:
- Winter season start date (say Dec 1st)
- Winter season end date (say, March 1st)

=> All the days which are between these two are considered winter days for any given year.

- Two different dates defining the beginning and the end of my PERIOD. The total length of the period may be more than one year.

HOW do I calculate total number of winter days for the PERIOD?

View 13 Replies View Related

Calculate Number Of Calendar Days For Each Month?

Mar 17, 2014

I have a list of months in column A and would like a formula in column B that calculates the number of calendar days for each month (i.e. Jan=31, Feb=28 etc.)

View 9 Replies View Related

How To Calculate Cumulative Time Over Different Days In Excel

Jul 28, 2014

calculate the running total of time spent in the attached spreadsheet.

View 8 Replies View Related

Calculate Number Of Days Using 2 Sets Of Dates

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

Using Countif To Calculate The Number Of Days In A Column

Dec 16, 2009

I am trying to use the following formula to count the number of times a day appears in a column. It doesn't seem to be working the way I want it to. =COUNTIF($A13:$A200,"Sunday"). It could be the way the days of the week are gathered on the sheet, you can see the attached workbook to see how I am using it.

View 4 Replies View Related

Calculate The Dates Between Two Days (excluding The Weekends)

Apr 29, 2006

I am looking for formula to calculate the dates between two days (excluding the weekends)
I am working in Saudi Arabia and the weekend days over here are thursday and Friday.

View 9 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







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