WORKDAY with Formulas, TIME Format & Work Hours

[kpeacock]

Can someone help with a workday question.
Analysis ToolPak is loaded & checked.

I have tried variations of the workday function without success.

My questions are:
Can you have a formula where Number of Workdays goes, if so how?
Can the format be TIME?
How do I address work hours?

Row 1
Col A = Start Date/Time = 9/5/08 4:00 PM Fri
Col B = End Date/Time = WORKDAY FORMULA GOES HERE
Col C = Duration = 1 hr 30 min

My cell format for Col A and B is mm/dd/yy h:mm AM/PM ddd
My cell format for Col C is h:mm.

Col B cannot be a Weekend or Holiday, and work hours 8 to 5
Expected Result is: 9/8/2008 8:30 AM Mon

This is an example of what I have that is not working.
(the holidays is working when just a NUMBER is entered where A1+C1 is in the formula)

IN ROW:1, COl:B =WORKDAY(A1,A1+C1,holidays)

 

[xld]

Assuming that it will never go over two weekends

=A1+A3+(WEEKDAY(A1+A3)=7)+(WEEKDAY(A1+A3,2)>5)

 

[barry houdini]

Hello kpeacock, welcome to MrExcel

Assuming you have daily start and finish times in E2 and F2, i.e. for your example

E2 = 08:00
F2 = 17:00

Then you can use this formula in C1

=WORKDAY(A1,CEILING((C1+MOD(A1,1)-E$2)/(F$2-E$2),1)-1,holidays)+MOD(A1,1)+C1-CEILING(MOD(A1,1)+C1-E$2,F$2-E$2)+F$2-E$2

Assumption is that A1 is a date and time within working hours, then formula should work for any (positive) time in C1.

 

[kpeacock]

Thank you xld..not sure where A3 came into the formula?

Thank you barry...just for clarification:
I have these to factors:

A1 is Start Date
C1 is how long the task will take

I am looking for the formula for B1...what will be my end date and time base on A1 and C1.

Your example has me putting the formula on top of my duration.

 

[barry houdini]

Sorry,

Yes I understand what you want to do....the formula is correct (I think ) but I said it should go in C1....I meant B1,.....obviously format B1 to show date and time as per A1

 

[kpeacock]

barry - you are brilliant = it worked.
I don't know why I did not think of ceiling.

thank you thank you

 

[James006]

Barry,

Sorry to resurrect this topic ...

But how on earth, can it be possible to modify your formula to account for a one-hour lunch break ???

Cheers

 

[dewilaz]

Hello mr Barry and others,

could someone please help me with task.
Formula below fits perfect only if start day is business day + start time is in work ours.
However, is it possible to adjust formula, that start day could be weekend or start time could be non work hours?

Hello kpeacock, welcome to MrExcel

Assuming you have daily start and finish times in E2 and F2, i.e. for your example

E2 = 08:00
F2 = 17:00

Then you can use this formula in C1

=WORKDAY(A1,CEILING((C1+MOD(A1,1)-E$2)/(F$2-E$2),1)-1,holidays)+MOD(A1,1)+C1-CEILING(MOD(A1,1)+C1-E$2,F$2-E$2)+F$2-E$2

Assumption is that A1 is a date and time within working hours, then formula should work for any (positive) time in C1.

 

[dewilaz]

Hi all,

I have solved this out by adding "help" column, which looks up if start date and time is in working hours period, if not, it gives next working day. So if task comes at work hours, formula below is used, if task comes after work hours, formula starts to calculate from next work day working hours.

Assuming you have daily start and finish times in E2 and F2, i.e. for your example

E2 = 08:00
F2 = 17:00

Then you can use this formula in C1

=WORKDAY(A1,CEILING((C1+MOD(A1,1)-E$2)/(F$2-E$2),1)-1,holidays)+MOD(A1,1)+C1-CEILING(MOD(A1,1)+C1-E$2,F$2-E$2)+F$2-E$2

Anyway, I need another calculation of exact time, how long did it take to complete task, EXCLUDING non working hours and weekends. Still don't have idea how to do it. Could anyone help me please ?

Hello mr Barry and others,

could someone please help me with task.
Formula below fits perfect only if start day is business day + start time is in work ours.
However, is it possible to adjust formula, that start day could be weekend or start time could be non work hours?

 

[dewilaz]

Hi guys,

any ideas?

I have start and end times of the task (DATE + TIME).
Work hours: 08:00 - 17:00
Goal is to calculate how long did it take to complete task (excluding weekends(holidays) and non working hours).

Could someone please help with calculation?

Hi all,

I have solved this out by adding "help" column, which looks up if start date and time is in working hours period, if not, it gives next working day. So if task comes at work hours, formula below is used, if task comes after work hours, formula starts to calculate from next work day working hours.

Assuming you have daily start and finish times in E2 and F2, i.e. for your example

E2 = 08:00
F2 = 17:00

Then you can use this formula in C1

=WORKDAY(A1,CEILING((C1+MOD(A1,1)-E$2)/(F$2-E$2),1)-1,holidays)+MOD(A1,1)+C1-CEILING(MOD(A1,1)+C1-E$2,F$2-E$2)+F$2-E$2

Anyway, I need another calculation of exact time, how long did it take to complete task, EXCLUDING non working hours and weekends. Still don't have idea how to do it. Could anyone help me please ?