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)

 

[dewilaz]

I found solution, case closed.

How to Calculate Net Work Hours Between Two Dates - Excel Tactics

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?

 

[Sergiusz]

Здравствуйте, kpeacock, добро пожаловать в MrExcel

Предполагая, что у вас есть ежедневное время начала и окончания в E2 и F2, т.е. для вашего примера

E2 = 08:00
F2 = 17:00

Затем вы можете использовать эту формулу в C1

=РАБОЧИЙ ДЕНЬ(A1;ПОТОЛОК((C1+MOD(A1;1)-E$2)/(F$2-E$2);1)-1;праздничные дни)+MOD(A1;1)+C1-CEILING(MOD(A1;1)+C1-E$2;F$2-E$2)+F$2-E$2

Предполагается, что A1 — это дата и время в пределах рабочего времени, тогда формула должна работать для любого (положительного) времени в C1.

Добрый день!
Не знаю почему, но у меня эта формула не работает.
Исходные данные - такие же:
А - дата начала
Б - дата завершения?
С - длительность работы в минутах
Время работы с 6:00 до 22:00, суббота и воскресенье - выходные, + праздничные дни.
Требуется вычислить время и дату завершения работы.

 

[Fluff]

Please post to this forum in English only.
If you wish to post in your own language, you need to start a thread in the Questions in Other Languages section of the board.
Thanks

 

[Sergiusz]

Good afternoon!
I don't know why, but this formula doesn't work for me.
The initial data is the same:
A is the start date
B is the completion date?
C - duration of work in minutes
Opening hours are from 6:00 to 22:00, Saturday and Sunday are weekends, + holidays.
It is required to calculate the time and date of completion of the work.