# How To Find Number Of Mondays Between 2 Dates Minus Holidays

Feb 8, 2012
I have a formula to find the number of Mondays between 2 dates.

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(E2&":"&F2)),"DDD")="THU"))

I now need to add in the variable of holidays and other days off. This is where I am loss. I do have a list of the weekdays that we did have class and a list of when we didn't

View 9 Replies
ADVERTISEMENT
Jul 6, 2012

I'm trying to count the total days between to dates but minus the fed holidays. I have a list of holidays I can ref. I like how the NETWORKDAYS function works but I need to include the weekends.

View 2 Replies
View Related
Apr 7, 2009

I wish to have a simple planner displaying the academic year with only mondays' dates.

ROW 3 displays the dates

Column B is Week 1

Cell B3 is the first date (10.08.09)

Cell C3 should read 17.08.09

Cell D3 should read 24.08.09

ETC

I have managed to do it using autofill before but I can't get it to do it now. Is there a preferred way to achieve this?

View 5 Replies
View Related
Dec 24, 2008

As before, please ignore the colored portions. They mean nothing. This is what I'm trying to do:

Every Monday the "minimum payment due" should reset to the same

as "payment per session" without affecting the rest of the columns.

This will help us in determining who owes us what, and how many classes they have paid for up-to-date.

View 14 Replies
View Related
Jun 15, 2012

I have a month and year entered in A2 in the format "mmm-yy". In B2 i need a formula which counts the number of mondays in that month.

View 5 Replies
View Related
Feb 23, 2014

Here is my set up:

A2 to BF2 is a range of dates

A3 to BF3 are sales. Days without sales are 0.00

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.

View 3 Replies
View Related
Jun 8, 2006

I have it like this

In column A is start of holidays

In column B is end of holidays

(one date written in A and one in B, and so on)

In C and D columns are start and end dates

Now I would like to check if start and and end dates of holidays, are between those dates in C and D

22/05/2006 24/05/2006

23/05/2006 24/05/2006

Check if first range of dates is between second one. Actually if holidays are between start and end date then check how many days are actually holidays in range of start and end dates

View 2 Replies
View Related
Feb 26, 2014

Is there a way to autofill dates in excel to exclude weekends and bank holidays?

View 6 Replies
View Related
Jul 25, 2014

find the difference between 2 dates in excel "date 1 - date 2"..... but I'm not quite sure as to how one would do it while excluding holidays!

View 8 Replies
View Related
Dec 5, 2013

I am trying to run a macro to put in the Month, Day, Year on each tab but I want it to exclude holidays and weekends. I am not a techie or anything but I would like to know how to do this. I have tried various vb codes but they don't exclude the weekends/holidays.

View 3 Replies
View Related
Nov 6, 2006

In my helpdesk spreadsheet, Column C has the date a request is received and Column D has the day it is signed off as complete.

This is used to compare how many days it takes before each request is completed.

Column B is usually blank, but, if the day a request is due to be completed is in the future, such as waterblasting set for 5 working days ahead, then the expected completion date is in Column B. As we don't want a report to show it took five days to complete, when 5 days was waiting for the booked job, we only want five days when we are working on it for five days.

So a typical request is received on Monday 1st January (C), completed on 3rd January (D), taking 3 working days to complete.

A less typical is a request received on Monday 1st January (C), booked to be done on the 4th January, (B). If the job is completed on the 4th, then that will be entered into (D). And we want it show as taking 1 day or less to complete.

I'm quite new to excel formulas , but would like to know the format for

IF B = Empty, then display the days (minus holidays and minus weekends) between C & D.

IF B = non-blank, then display the days (minus holidays and minus weekends) between B & D.

I've tried to play with the Workday function but it and the format of the IF function seem just beyond my grasp, depsite it simplicity, so hopefully a bit more insight as i get to grips with formulas would help me out...

View 9 Replies
View Related
Apr 20, 2009

I attached an excel file for an example. Basically I am calculating on time shipping, but I want don't want Saturday and Sunday to coun't against us.I need G5 to equal 3 days. Right now when I subtract E2 from F2 I get 5 days. But we only work M-F, and Saturday and Sunday shouldn't count against us. How would I subract the days automattically, so it takes out Saturday and Sunday?

View 10 Replies
View Related
Oct 23, 2007

I have a static date and military time in B6 (5/10/07 18:00) I have to write various formulas to reflect 1 month (whatever calendar month it ends up in, so not necessarily 30 days) minus 7 days and then the same formula plus 7 days. How is this written?

View 9 Replies
View Related
Apr 18, 2013

Calculating Lead time (in hours) between two dates/times, excluding holidays and weekend

Start Time

End Time

Lead Time

12/26/2012 15:50

1/2/2013 12:38:00

??????????

View 4 Replies
View Related
Feb 13, 2008

I have a spread sheet that is used to show the dates for the days in the week that my students are in class. I want it to be able to take the date from a cell that has the class start date and then show what the date will be for day 1, day 2, day 3, etc... I am not a beginner with excel but i can't figure out how to get it to skip calendar weekends.

View 9 Replies
View Related
Jun 17, 2014

i have column A and B with list of dates. Column A has earlier dates when compared to column B.

i am trying to find a formula with which i can know the difference between the dates in "number of months".

Ex: Column A has 06/01/2014 and Column B has 08/30/2014. The difference between the dates in terms of month is 3 which i need in a formula.

View 5 Replies
View Related
Apr 17, 2014

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.

View 9 Replies
View Related
Aug 6, 2013

I am trying to capture the number of years/quarters are between two dates - giving credit for one day worked into the quarter.

In the attached png file you can see the formula I used for column J. This is the same formula I need to use for Column R but now using 10/1/11 and the date provided in Column R. The formula should be used in Column S "Part B" so that I can determine the number of years between 10/1/11 and the date in Column S.

View 7 Replies
View Related
Feb 11, 2013

How to find out the number of cells in a range containing dates less than a specified date.

View 4 Replies
View Related
Jul 11, 2007

I have many regularly used workbooks with financial data in them. Many times the financial data needs to be adjusted due to rounding issues. To do this I add a +1 or -1 or sometimes it is in decimal form +/- .1 Also the number "1" is not always (but almost always) used. There are some circumstances where I may add + 2 or +6 or any small number to the end of a formula.

My problem is that when I use the file again after making these changes, I am wanting to remove the "adjustments" I have made to the formulas. Sometimes a green triangle will appear in my cell telling me the formula does not match that of other near-by cells, and that is good, but it is not consistent enough to find them all, or even most of them.

What I am wanting is a macro I can run that will identify any cell with these "adjustments" I have added to them and I will then go in and delete the +/- 1 or whatever the number is. I can easily do a Find "+1" for the entire workbook, it is the range of possible numbers that is preventing me from doing a simple ctrl + f.

View 9 Replies
View Related
Nov 29, 2013

I have date range, create and fixed date as follows:

Create Date

Fixed Date

10/1/2013 17:36

10/25/2013 20:33

I have list of holidays as bellow:

1/1/2013

3/29/2013

4/1/2013

5/1/2013

[Code] .....

I need a result which shows how many holidays w/in the date range. The result i should be 1.

View 3 Replies
View Related
Oct 7, 2011

I need to calculate the number of days between 2 dates including holidays and weekends. That means I cant use NETWORKDAYS as it excludes weekend.

View 9 Replies
View Related
Dec 2, 2008

In an excel file i have two fields,

Month to Start - is a combobox with 12 months, next is

Month to go back - is a textbox, here specifying number of months to go back.I need to store month names by minus the existing month - number in a textbox

eg: if Month to Start is 'September' and Month to go back is 2, then what i need is September & August in an array or separate variables.

How I can minus number from a month?

View 9 Replies
View Related
May 16, 2014

My aim is to find the end working date for each task, as well as the next working date for the next task. The working days for this employee are only Monday (6hours), Wednesday (6 hours) and Friday (8hours).

D

E

F

G

H

Start Date

Duration

Completed Days

Remaining Days

End Date

4

5/05/14

3

=IF(TODAY()>=H4, E4,IF(TODAY()

View 3 Replies
View Related
Sep 25, 2009

I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010.

=IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)-4.5

From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula

*Note*

'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.

View 2 Replies
View Related
Mar 8, 2014

I am having trouble setting up my cycle count spread sheet. For instance if my starting number is 1000 and when it reaches 0 or below it starts to count in minus when I just want it to automatically reset to 1000

View 3 Replies
View Related
Feb 14, 2013

I have a column of numbers which have the - sign at the back end of the number instead of the front. What would be the easiest way to turn that into a negative number without manually keying each one?

View 4 Replies
View Related
Dec 1, 2006

I would like to count how many Mondays, etc., that shh worked.

DateDayDVMClinic

11/01/06WedshhCal Vet Neuro

11/16/06ThushhCirby Ridge

11/22/06WedshhHardin Animal

11/27/06MonshhCrossroads-DS

11/24/06FrishhBear River

11/21/06TueshhLaguna Creek

11/22/06WedshhRoseville Vet

11/19/06SunshhMissouri Flat Pet

11/20/06MonshhCrossroads-Folsom

11/20/06MonshhSierra Vet-Stockton

11/20/06MonshhAnimal Clinic-Benicia

11/22/06WedshhBlue Ravine

11/24/06FrishhCat Clinic-Folsom

11/18/06SatshhAmerican River

11/21/06TueshhIndian Creek Vet

11/28/06TueshhSlate Creek Animal

Some dates will appear twice, because that will be relative to different clinics.

View 9 Replies
View Related
Jun 5, 2013

I created a report that runs each weekday morning, using data from the previous workday.

I have written code that saves the file into a LAN directory as "BEST CASH MM-DD-YYYY" with the variables being the previous day's date.

My problems is that when I run the report on Monday morning using Friday's data, of course it's including Sunday's date and not that previous Friday...

Here's the test code I'm working with right now; I'm using a folder on my desktop until I get the code right, then I'll change it to the proper LAN directory:

ChDir "C:UsersC700MDesktopTEST"

ActiveWorkbook.SaveAs Filename:= "C:UsersC700MDesktopTEST" & "BEST CASH " & Format(Date - 1, "mm-dd-yyyy"), FileFormat:= xlOpenXMLWorkbook, CreateBackup:=False

View 7 Replies
View Related
Feb 1, 2014

I am trying to create a formula, but my knowledge of excel formulas is very new! I am trying do create an excel formula which calculates the difference in video view data which I enter, comparing today to yesterday to see the growth.

My column A is dates. My column B is video data.

Is there a way Excel can work out the TODAY date's data and minus YESTERDAY date's data to find the growth?

My thinking is that this would look something like the following, but I'm not sure how it would be written in Excel's formua:

Where Column A is = TODAY (), conduct sum where TODAY () minus data from yesterday (TODAY () -1).

-Find TODAY ()

- FInd TODAY'S associated data

- Find YESTERDAY or TODAY -1

- FInd YESTERDAY'S associated data

- Minus Yesterday from Today to find the difference.

View 5 Replies
View Related