Subtracting Dates (minus Weekends)

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


ADVERTISEMENT

Automatically Assigning Dates To Cells Minus Weekends

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

Calculate Hours Worked, Minus Weekends

Nov 11, 2009

to calculate how long a ticket is open in our system before being resolved. I don't want to count weekends, and if the ticket is 'suspended', I don't want to count that either. There is also the factor that the ticket 'un-suspend' date may be later than the ticket 'closed' date. Which is the bit that's throwing me.

So, I have the following fields

Ticket Open, Ticket Closed, Ticket Suspended Date, Ticket Unsuspended Date

A sample ticket might be (using above fields)

02/11/09 09/11/09 04/11/09 30/11/09

That 'should' equal two days (16 hours) as the Unsuspend date falls after the close date so it was suspended from the 4th until closure.

Now I want to know, in hours (8 hour day) how long that ticket took to resolve (i.e close), remembering you can't count the time it was suspended, or any time that fell over a weekend. Also not all tickets are suspended.

View 13 Replies View Related

Subtracting Two Dates

Mar 27, 2009

What would the formula be for subtracting two dates to get the numbers of years, months? I cannot get my formula to work.

Also, can Excel take a telephone number such as (555)-555-5555 can place the phone number in another field such as 555-555-5555?

View 9 Replies View Related

Subtracting Dates :: Birthday

Feb 22, 2010

On my worksheet I have a date of birth formated yyyy-mm-dd. On my form, I want to be able to identify anyone younger than 16. I tried subtracting from today's date on the worksheet, but that didn't work too well for me.

View 2 Replies View Related

Subtracting Weekending Dates

May 30, 2007

I have a weekending date. I would like to subtract X amount of weeks from this date and end up with the weekending date less the weeks I subtracted.

View 10 Replies View Related

Subtracting Time Between Two Dates

Apr 17, 2012

I am trying to subtract 45min from the time 00:05, but the result i am getting is 0:40 which is wrong.

I am using below formula.

=TEXT(ABS(B3-$A$3),"-h:mm")

Actual timeTime minusResults00:0500:45-0:40

View 1 Replies View Related

Adding/Subtracting Dates

Mar 10, 2008

1) To be able to get the current date in the format of 3/10/2008

2) To be able to take dates from 2 cells and get an integer value for the number of days between the two dates

View 9 Replies View Related

Calculate End Dates Allowing For Weekends

Feb 9, 2009

If I have the start date (say February 01, 2009) and the job will take 100 working days, when will it finish if there is no work done over weekends? I assume you can use networkdays somehow but the final logic escapes me

View 2 Replies View Related

Add Worksheet With Dates As Names & No Weekends

Oct 2, 2006

Is it possible to make a macro that creates a Pop-up box that asks for date ranges and generates copies of the first worksheet in the workbook labelled as dates? There are ALWAYS 6 half terms to a school year so a 6x2 pop up box / table that is formatted in the following way will allow the appropriate date ranges to be inputted (saturdays and sundays must be excluded from this range):

Title of input box: Input School Half Term Date Ranges

******** Start Date ** End Date **
Autumn 1 03.09.06 18.09.06
Autumn 2
Spring 1
Spring 2
Summer 1
Summer 2

The macro would then create a series of sheets from 03.09.06 to 18.10.06 as well as the other 5 date ranges (excluding weekends) using the first sheet as a template. Would the user have to also input the start day for each half term or would excel 'know that 04.09.06.06 is a Monday and 18.10.06 is a Wednesday?

If the macro could create worksheets for these date ranges and clone the first worksheet in the workbook then that would save me LOADS of time copying and pasting each year.

View 9 Replies View Related

Days Between 2 Dates Excluding Weekends

May 22, 2008

how to find the number of days between 2 Dates excluding weekends and holidays using vba. I m using excel 2003. All I know is to figure out todays date using the keyword 'Date'.

Also, this is my second post and I have tried to comply with the rules regarding the title, but if it is still inappropriate and does not meet 'stating the problem' criteria,

View 3 Replies View Related

Calculating Dates Using Plus Minus Days

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

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 Difference Between Dates Excluding Weekends

Jun 8, 2008

I wanted to calculate the difference between dates, excluding the weekends. After much thought i used =NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,-SIGN(NETWORKDAYS(A1,B1)),0) that seemed to be working fine. However, I had a problem today with this formula when my start day was saturday (24/05/08) and the last day was monday (26/05/08). What i want is that when i take the difference between these two days, i should get zero as answer as we are not including the weekends in calculation so the start date should ideally be the first weekday which is Monday. However, the formula is giving me 1 as an answer.

This formula works fine if i select 27/05/08 as last date in which case i get 2 as an answer which is right. In gist, what i am looking for is a formula which will give me the difference between two dates and exclude the weekends from the calculation. The last date can't be a weekend, but the start date can. Hence, whenever the start date is a weekend, the formula should take monday as a start day.

View 9 Replies View Related

Counting Days Between Two Dates Minus Holidays

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

Subtracting Early Dates To Get A Number Of Days

Sep 19, 2007

I wish to subtract the date 11/18/1877 from 11/16/1968 to get a result showing the number of intervening days but excel does not want to do it.

When I take 11/18/1900 from 11/18/1968 and format the result to get a number with no decimals the result is 24835 as it should be; but it does not seem to work for dates earlier than 1/1/1900.

View 9 Replies View Related

Subtracting Dates :: Percentage Of The Days Worked

Dec 1, 2008

I have a minor assignment at work. The manager has given me a sheet that lists each employee's start date, and he wants to know what percentage of them have worked here for more than 30 days, 60 days, 90 days, etc. I know a long way to do it, but there's several hundred names, and there must be a quicker way. Is an array formula the answer? I need a non-VBA answer, but I'm not opposed to learning a VBA method (no pun intended).

View 9 Replies View Related

Vba Type Mismatch When Subtracting Dates/times

Sep 27, 2006

Does anyone know why I get a type mismatch on the following? I am trying to subtract one time from the other to get the total hours worked.

Sub WorkedHours()
Dim vStart, vEnd As Date
Dim vHours As Integer

vStart = "11/09/06 22:00"
vEnd = "11/09/06 23:30"

vHours = (vEnd - vStart) * 24

End Sub

View 3 Replies View Related

Autofill Dates Excluding Bank Holidays & Weekends?

Feb 26, 2014

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

View 6 Replies View Related

Auto Name Tabs With Dates Excluding Weekends And Holidays

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

Calculate Only Working Hours Between Two Dates Excluding Weekends

Oct 30, 2009

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.

View 9 Replies View Related

Add X Days To Dates, For Criteria Count, But Skip Weekends

Feb 28, 2008

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.

Example:

D4 --------- K4
8/11/2007 -- 2
9/11/2007 -- 3

Expected output:

AE4---------AF4
8/11/2007 -- 1
9/11/2007 -- 2
12/11/2007 --1
13/11/2007 --1

View 5 Replies View Related

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

Formula For Working Out Number Of Days Between Dates Excluding Weekends?

Aug 6, 2012

I want to create a formula that works out the number of days between two dates but excludes weekends?

View 3 Replies View Related

Difference Between 2 Dates, Incl Weekends, With Variable Work Days

Jul 11, 2006

I am creating a problem report spreadsheet for work. In col C I have a date/time that the problem started, eg, 23-Jun-2006 15:20 and in col D I have a date/time for when the problem was resolved, eg, 26-Jun-2006 15:40. I need to calculate the amount of hours that we were not available for use (due to the problem). This is based on a list of available hours. Eg, on Mondays we are available 19.5 hrs, T-F 22.5 hrs, Sa 14.5 hrs, and Sun 0 hrs.

If I do a straight subtraction, it uses 24 hrs for each day - not taking into account our available hrs. If I do NETWORKDAYS it automatically doesn't consider Sat or Sun - and i need it to - and it does have a DayEnd and DayStart but it assumes they are constants. I found the day corresponding to the date and thought I could determine the series of days between it, e.g., if the start day was Friday & the end day Monday, the series in between is Sat & Sun. Then I could do a lookup on all 4 days in a table & add them all together. But I can't figure out how to do this!

View 4 Replies View Related

Subtracting Dates Results In A Big Number When A Date Field Is Blank

Jul 11, 2009

How can I change the ....

View 14 Replies View Related

+ 0.1 Minus

Jan 20, 2010

I have part if the code here’s
PHP
Range("f" & indexI + 1).Value = Format(Strl, "00:00") 

to return value = time formats
How do I modify it to + 0.1 minus?

Sample
IF the return value = 12:00:00
Now I wanted = 12:00:01

View 9 Replies View Related

Time Out For Weekends

May 24, 2008

I need to count the hours between dates eliminating the 48 hours for the weekend starting at 0:700 Saturday and recommencing at 0:700 Monday morning.

View 9 Replies View Related

How Do I Take Out Holidays And Weekends

Sep 21, 2009

I am trying to figure out how to take holidays and weekends out of my data.

View 9 Replies View Related

Recognizing THE Weekends

Sep 28, 2009

I have a spreadsheet which will be used to measure raw material consumption by day.

I wish to list all days of a year down a column - that I can do.

However, I would like to conditionally format any cell (preferably the row) which contains either a Saturday or a Sunday.

View 9 Replies View Related







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