Excel Formula Help For Working Hours

October 23, 2018 at 06:29:37
Specs: Windows 8
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


See More: Excel Formula Help For Working Hours

Reply ↓  Report •

#1
October 23, 2018 at 07:11:03
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


Reply ↓  Report •

#2
October 23, 2018 at 08:10:43
i also need formula what would include Saturday

To 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.INTL

http://www.cpearson.com/excel/bette...

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


MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
October 23, 2018 at 08:30:20
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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Related Solutions

#4
October 23, 2018 at 08:43:05
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,



Reply ↓  Report •

#5
October 23, 2018 at 09:25:41
Something that might work

This 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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#6
October 23, 2018 at 09:39:22
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

http://www.skeptic.com/


Reply ↓  Report •

#7
October 23, 2018 at 10:15:56
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

http://www.skeptic.com/


Reply ↓  Report •

#8
October 23, 2018 at 15:31:01
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/2018

Cells 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

http://www.skeptic.com/


Reply ↓  Report •

#9
October 24, 2018 at 04:16:32
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


Reply ↓  Report •

#10
October 24, 2018 at 04:31:02
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

http://www.skeptic.com/


Reply ↓  Report •

#11
October 25, 2018 at 02:55:19
Hey thanks a lot, much appreciated. i will await your feedback.

Reply ↓  Report •

#12
October 25, 2018 at 19:47:46
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 minutes Before your 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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#13
October 26, 2018 at 06:50:41
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

http://www.skeptic.com/


Reply ↓  Report •

#14
October 27, 2018 at 12:34:35
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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Ask Question