Need a formula (E1) to determine the number of days that have passed between two given dates (C1 & D1) ONLY when the Status of a given report (B1) is "Late"

B1 = Status (Pending; On Hold; Late; Completed) (i.e "LATE") C1 = Status Date (i.e. 28 Mar 14) D1 = Deadline (date) (i.e. 25 Mar 14) E1 = Total Days Late (= ?)

I need to figure out how to tell Excel to count the number of invoices on a worksheet older than 30 days and display that number in my Summary worksheet. Here's a quick example:

Worksheet '1234' has 10 invoices, 6 of which are more than 30 days old (invoice dates are in column B). Worksheet 'Summary' needs to display the total number of invoices and the number older than 30 days.

I have been using a formula to count the number of days between 2 dates and if the date was older than 12 months it would work fine.

The data that i was using has been changed and they have decided to change the format of the dates and dropped the year reference no all i have to work with is dates like 27-07 this represents 27th July i can format this date to work fine unless the date falls before 1 jan this year all i get is lots of numbers and it stuffs up the whole calculation.

Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date

But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero

Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!

I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.

I wish to calculate the days between the starting date (column A) and ending date (column B). For the first 7 days are excluding all the holiday and weekend and the rest of it until the ending date are counted.

I am trying to count the number of dates in a column that are within the last 180 days. I tried using COUNTIF and it did not work. Formula which I thought should work: =COUNTIF(A3:A32,>(A40)). My workaround is shown in Column B, but this method adds one column for each participant.

How to create a spreadsheet with what I think will be a very simple formula?

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2. If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2. If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

OR

Another, maybe simpler, way of saying it is:

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2. If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2. If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

I work only on Wednesdays and Thursdays. I'd like to calculate the number of my working days between two dates.

NETWORKDAYS seems like the right place to start, but I can't think of how to tell it to ignore Mondays, Tuesdays and Fridays as well as the weekend days.

I am trying to compile a count of dates using a numerical reference which adds x consecutive dates onto the initial start date based on the number in a cell.The dates added cannot fall on a weekend.

The start date for each calculation begins in D4 with the corresponding number of days to add onto this date in K4. In the example below 8/11/2007 -- 2 means that the 8th and 9th of Nov get one added to their count. As the next row also has 9th Nov as a start date one more is added to 9th Nov. As the 10th and 11th were a weekend they are skipped and the count starts again from the next Monday. I have enclosed a simplified worksheet with some sample data.

I have a start date (say 01/04/07) and I need to add a number of days to it to get the start of the next financial period. Unfortunately, I need to ignore a small list of dates (bank holidays, etc).

I am trying to set up a function to derive a number of days between 2 dates.

Within the table I have an on hire date, and off hire date, and an optional suspend hire date range in 2 other columns - ie. there are 4 columns with dates in.

On hire is in column B; offhire in C; suspend hire in G and recommence hire in H. The optional suspend hire date range will always fall within the main hire date range.

Column I calculates the number of days for the suspend hire period.

My key cut off date is in cell C4.

There are 3 different scenarios I need the function to cover.

1 - if the cut-off date (in C4) is later than recommence hire, calculate the number of days from hire start to cut-off date, less suspend hire period days.

2 - if the cut-off date (in C4) falls within the suspend hire period, calculate the number of days from the hire start to the begining of the suspend hire period.

3 - if the cut-off date (in C4) falls before the suspend hire period, calculate the number of days from the hire start to the cut-off date.

I couldn't find a solution to my problem in the forum: Is there are formula that will allow me to calculate number of days between two dates? The date format is eg. 07/06/07

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.

I have attached a workbook for example, the dates im looking to find difference between is marked in yellow...Need result in column E, would be nice with a formula you can just drag down...

Tried doing this myself, but the dates have weird formatting in these documents, so not sure how to deal with it.

I'm currently working on a file that has employee holidays/sickness etc. What I want to do is be able to see is a DATE FROM and DATE TO and next to it the total amount of working days between and including those dates. On top of the since employee's sometimes like to take half days I need it so that it can determine half days as well.

So for example I have an employee going to be off work from 29/10/2007 until 09/11/2007 12pm which is 10.5 days in total, but that includes weekends when I don't want it to.

Also it comes out as 11 days at the moment because I can't figure out how to get it to determine is it is half a day.

What formula should I use or if someone could create an example that would be really usefull :D

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.

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

I wanted to determine the number of days between two dates. Specifically, if the initial date is in one month, and the second is in a different month and an output would result a number of days in each month until the final date. BUT I have a large amount of data to do this for in a list view, way to put a formula in excel and just drag down the entire list to get the required information. see below for an example.

The result I'm looking for is the separate the months and only show the relevant months between the two dates in one cell or the adjacent. Something similar to the table outlined below.

I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.

I'm working on building a workbook to track sales progress, and I'd like to perform an analysis on some of the data with regards to sales efficiency.

To simplify: Column A = Initial Contact Date (mm/dd/yyyy) Column B = Close Date (mm/dd/yyyy)

As an example, I would like to calculate the average number of days between the Initial Contact Date and the Close Date to calculate the average number of days in the prospecting cycle. Basically, the formula should function as AVERAGE((B1-A1)+(B2-A2)+(B3-A3)...) and so on for the entire column.

I know one solution would be to insert a new column that performs the subtraction between the two dates, and then I could AVERAGE this new column. However, I want to perform similar calculations to analyze the time period between other key milestones in the prospecting cycle - rather than have a lot of extra columns in my sheet, I was wondering if there's some type of array formula that will calculate what I need.

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.