Hey All, Maybe someone here knows how to set up formula to calculate working hours between two dates and times?

I have already formula for standard Monday to Friday set up with starting time 9:00am and end time 17:00pm. However i also need formula what would include Saturday with different working time 9:00am and 15:00pm.

This is my current formula but i cannot seem to figure out how to add Saturday and wit different working times.

=(NETWORKDAYS(OpenDate_time,ClosedDate_time,Holidays)-1)*(END_TIME-START_TIME)+IF(NETWORKDAYS(ClosedDate_time, ClosedDate_time),MEDIAN(MOD(ClosedDate_time,1),END_TIME,START_TIME),END_TIME)-MEDIAN(NETWORKDAYS(OpenDate_time, OpenDate_time)*MOD(OpenDate_time,1),END_TIME,START_TIME)

Thanks in advance

I'd like to offer a posting tip: When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "Formula Help Needed" we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

i also need formula what would include SaturdayTo include Saturday as a regular workday, you will have to use the NETWORKDAYS.INTL() function,

which became available on the 2010 version.

with different working time 9:00am and 15:00pm.

Since NETWORKDAYS.INTL() knows nothing about times,

the best I can think of is you would have to wrap the formula in a beginning IF() to check whether the date is a Saturday and do a different time calculation.If you are still using a version prior to 2010 here is a VBA program

that may help you get similar functionality as NETWORKDAYS.INTLhttp://www.cpearson.com/excel/bette...

I am still using Excel 2007, so I do not have access to NETWORKDAYS.INTL()

MIKE

The more I think about it, the less I think it can be done.

The IF() wrapper might work for one Saturday, but if you have more than one, I don't know how the time could be accounted for in the formula.You would probably have to do a complete second formula just to account for Saturdays and then add the result of your Saturday formula to your Regular week.

It's the different Start Time & End Times that are the major problem.I think you are going to need a VBA solution.

MIKE

message edited by mmcconaghy

Hey so far in my research i did update the formula with NETWORKDAYS.INTL and specified that only day of weekend is Sunday with 11. Now it looks like that: =(NETWORKDAYS.INTL(OpenDate_time,ClosedDate_time,11,Holidays)-1)*(END_TIME-START_TIME)+IF(NETWORKDAYS(ClosedDate_time, ClosedDate_time),MEDIAN(MOD(ClosedDate_time,1),END_TIME,START_TIME),END_TIME)-MEDIAN(NETWORKDAYS(OpenDate_time, OpenDate_time)*MOD(OpenDate_time,1),END_TIME,START_TIME)

At least now i have hours counted on Saturday.

In fact i came across another version of the formula however it has one issue, it is counting also out of business hours.

=SUMPRODUCT(INT((WEEKDAY(B6-W$2:W$8)+INT(C6)-INT(B6))/7),Z$2:Z$8)-SUMPRODUCT((J$2:J$10>B6)*(J$2:J$10< C6),LOOKUP(WEEKDAY(J$2:J$10),W$2:Z$8))+MOD(C6,1)-MOD(B6,1)+LOOKUP(WEEKDAY(B6),W$2:X$8)-LOOKUP(WEEKDAY(C6),W$2:Y$8)

W$2:W$8 – Weekday number from 1- 7

Z$2:Z$8 – Formula =Y2-X2

J$2:J$10 – Holiday dates list to be excluded

X$2:X$10 – Start times per each day of the week

Y$2:Y$10 – End times per each day of the week

Maybe any ideas how to update this formula so it excludes the out of business hours?I'm fairly new this type of formulas so there might be something simple i don't see :)

I'm using Excel 2013.

Thank you,

Something that mightworkThis formula will give you the number of Saturdays between two dates:

=SUM(INT((WEEKDAY(StartDate-7)+EndDate-StartDate)/7))

Since there is only a two hour difference between your Regular times and Saturday times

just multiply by 2=SUM(INT((WEEKDAY(StartDate-7)+EndDate-StartDate)/7)) * 2

That gives you the difference in time, then subtracting this from your total hours might work??

Untested so not really sure.

MIKE

message edited by mmcconaghy

Problems will arise with the previous formula

if the Ending Date is a Saturday and the Ending Time is before 15:00.Another exception will need to be accounted for somehow.

MIKE

On further consideration, I think the subtraction formula will work ok. Post a small sample of your data, after reading this HOW-TO which explains the use

of the < PRE > tags to align your rows & columns, and please use Row Number

and Column Letters.https://www.computing.net/howtos/sh...

I'm going to be using the NETWORKDAYS() function mentioned in reply #2

If your going to use one of the other formulas, that do not use NETWORKDAYS let me know so I can use the same one.

MIKE

This will give you the number of Hours, including Saturdays: With your data like:

A B C D E F G 1) Start Date & Time End Date & Time Hrs:Min Reg Sat Holidays 2) 11/05/2018 10:15 11/26/2018 11:00 138:45 Day_Start_Time 9:00 9:00 2/16/2018 3) Day_End_Time 17:00 15:00 3/25/2018 4) Diff_Time 2:00 7/4/2018 5) 9/17/2018 6) 12/25/2018Cells E2 & E3 are the Regular Sart & End Times

Cells F2 & F3 are the Saturday Start & End Times

Cell E3 is the Time Difference. Simple formula of: =(E3-E2)-(F3-F2)

That way if the Saturday Times ever change the formula won't have to be updated.In Cell C2 enter the formula:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$G$2:$G$6,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($E$3-$E$2)*(MAX(

NETWORKDAYS2((A2+1),(B2-1),1,$G$2:$G$6)

,0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($E$3-$E$2))/(24*($E$3-$E$2))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$E$2)+(24*$E$3-(24*(A2-INT(A2)))),2),ROUND((24*($E$3-$E$2)),2))))))/24-(SUM(INT((WEEKDAY(A2-7)+B2-A2)/7))* E4)Replace the NETWORKDAYS2() function with your NETWORKDAYS.INTL()

See how that works for you.

MIKE

Hey, thank you for your time and looking into this. I tried your formula and it works fine on counting the hours however the problem what i have with this version is that it also counts hours witch are outside business hours. here is an example from my data:

A B C D E F G Open_Date Close_Date Hours Reg Sat Holidays 1 01-10-18 0:05 02-10-18 11:32 19:27:36 Start 9:00 9:00 10-10-18 2 01-10-18 6:25 01-10-18 8:42 2:17:24 End 17:00 15:00 3 01-10-18 6:35 01-10-18 7:53 1:18:00 Dif 2:00 4 01-10-18 7:15 01-10-18 13:23 6:07:48 5 01-10-18 8:10 01-10-18 11:57 3:46:48 6 01-10-18 8:30 01-10-18 17:14 8:43:48 7 01-10-18 8:50 01-10-18 16:59 8:09:00 8 01-10-18 8:55 02-10-18 12:58 12:03:36 9 01-10-18 9:25 02-10-18 11:16 9:51:00 10 01-10-18 9:30 01-10-18 16:43 7:12:36 11 01-10-18 9:30 01-10-18 16:52 7:22:48 12 01-10-18 9:35 01-10-18 14:55 5:19:48 13 01-10-18 9:35 01-10-18 16:10 6:35:24 14 01-10-18 9:45 01-10-18 14:48 5:03:36

For example in the first Row the total hours are 19:27:36 however according to the business hours it should be 10:32:48 (8 working hours from 01/10/2018 and 2:32:48 hours from 2/10/2018) Basically the idea is to see how many working hours it takes to close the task.Let me know if this makes sense.

Thanks,

message edited by LeMe

I am not home today or tomorrow,

so it will be a few days before I can work on this,

I'll get back to hopefully by Friday.MIKE

Hey thanks a lot, much appreciated. i will await your feedback.

Well, at first glance, the problem is the actual Times, as shown in your examples. In your original post you requested:

how to set up formula to calculate working hours between two dates and times?The very first time in your list is 0:05,

which is 5 minutes past Midnight

which is 8 hours and 55 minutesBeforeyour work day Start Time.Are the Times correct?

In your last post you say:

how many working hours it takes to close the task.Do you want only the Work Day Hours as it relates to the Task?

In other words, a task can start before the work day begins

and end after the work day ends, but you want only the time

between 9 & 5, regardless of how long the task acually takes?Will need some clarification on just what it is your looking for.

MIKE

message edited by mmcconaghy

Another question.

In all your sample data,

no task ran beyond 20 hours

and was never more than two consecutive days

and only Monday & Tuesday.Is this a valid sample or can a task run for more than two days?

Can a task begin on a Sunday or end on a Sunday?

Need to know if we have to account for Days as well as Hours/Min.MIKE

This is a possible solution. Put the formula in cell C2 and drag down:

=IF(OR(WEEKDAY(A16,2)=7,AND(MOD(A16,1)>MOD($E$3,1),MOD(B16,1)>MOD($E$3,1)),AND(MOD(A16,1)<MOD($E$2,1),MOD(B16,1)<MOD($E$2,1))),0,IF(AND(MOD(A16,1)<MOD($E$2,1),MOD(B16,1)<MOD($E$3,1)),MOD(B16,1)-MOD($E$2,1),IF(AND(MOD(A16,1)>=MOD($E$2,1),MOD(B16,1)<MOD($E$3,1)),MOD(B16,1)-MOD(A16,1),IF(AND(MOD(A16,1)<MOD($E$2,1),MOD(B16,1)>MOD($E$3,1)),8/24,"")))+((NetWorkdays2(INT(A16),INT(B16),1,$G$2:$G$6)-1)*8)/24)+IF(WEEKDAY(A16,2)=6,MOD($F$3,1)-MOD(B16,1),0)

Don't forget to replace my NETWORKDAYS2() function with your NETWORKDAYS.INTL() function

See how it works for you.

MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History