Solved: How to format workday end date with 17:00

[ioncila]

Hi
I´m having difficulties to the following:

I have a sheet with workday formula - e.g., in "M22", WORKDAY(L22;K22;Holiday) - that I thought it worked properly since I discovered that M22 format is dd/mm/yy 0:00, instead of dd/mm/yy 17:00.

Obviously, L22 is the start date (formated dd/mm/yy 8:00) and K22 the number working days.

How do I format the end date with 17:00?

Thanks very much in advance
Ioncila

[Bob Phillips]

Try

=WORKDAY(L22;K22;Holiday)+MOD(L22;1)

[ioncila]

Thanks for your reply.
It did change something. By your suggestion, M22 format changed to dd/mm/yy 8:00. But still I wish it could be 17:00 for a calculation that matches the result I want.
What is missing in formula?

[Bob Phillips]

What is in L22 and K22?

[ioncila]

L22 is the start date and K22 is the number of working days.

L22 format is dd/mm/yy 8:00. K22 format is general
For the goal result I want, M22 format must be dd/mmm/yy 17:00.

Formula M22 = WORKDAY(L22;K22;Holiday):

L22 = 06/05/11 8:00
K22 = 2
Result in M22 must be 07/05/11 17:00

[Bob Phillips]

I don't get why M22 should be 17:00 when L22 is 08:00.

[ioncila]

Considering a workday = 08:00 to 17:00,
For example if I count 2 workdays fro 06/05/11 08:00, it should result 07/05/11 17:00, but what happens actually is 08/05/11 08:00.

Please see the attached file.

Thanks
Ioncila

[BrianMH]

The workday function only uses whole days. You can't get partial days. What if you say you start something at 14:00 for 1.5 days are you wanting that to go into the next day or the day after?

[BrianMH]

[vba]Function workdaystime(StartDate As Date, WorkDays As Double, Optional StartTime, Optional EndTime, Optional Holidays)
If IsMissing(StartTime) Then StartTime = TimeValue("09:00")
If IsMissing(EndTime) Then EndTime = TimeValue("17:00")
Dim WorkingTime As Date
Dim DateTemp As Date
Dim TimeTemp As Date
Dim Days
Dim PartDays
Dim InitialTime As Date

Days = Int(WorkDays) - 1
PartDays = WorkDays - Days


WorkingTime = EndTime - StartTime
PartDays = WorkingTime * PartDays
InitialTime = TimeValue(StartDate)
If InitialTime = 0 Then
InitialTime = StartTime
End If



TimeTemp = InitialTime + PartDays
If TimeTemp > 1 Then
Days = Days + 1
TimeTemp = TimeTemp - 1
End If


If IsMissing(Holidays) Then
DateTemp = WorksheetFunction.WorkDay(StartDate, Days)
Else
DateTemp = WorksheetFunction.WorkDay(StartDate, Days, Holidays)
End If

If TimeTemp > EndTime Then
TimeTemp = TimeTemp - EndTime
TimeTemp = StartTime + TimeTemp
If IsMissing(Holidays) Then
DateTemp = WorksheetFunction.WorkDay(DateTemp, 1)
Else
DateTemp = WorksheetFunction.WorkDay(DateTemp, 1, Holidays)
End If

End If

If TimeTemp < StartTime Then
TimeTemp = StartTime + TimeTemp
End If


DateTemp = DateTemp + TimeTemp
workdaystime = DateTemp




End Function

[/vba]
This udf should work for you. I have attached yourspreadsheet with the formulas to compare. If any of you super experts want to check for anything I haven't thought of that would be cool. This might be useful as a KB entry as I have seen this sort of request before.

[ioncila]

That's it. It solved my problem.

Thank you very much Brian.

[BrianMH]

Your welcome

[Bob Phillips]

I still don't get it. Just as 24:00 on say 3rd May is the same instant of time as 00:00 on 4th May, your situation suggests to me that 4th May 17:00 is the end of one day and 5th May 08:00 is the start of the next, so in working day terms they are the same instant of time. So I cannot see why 2 days beyond 3rd May 08:00 is not 5th May 08:00. What is 2 days beyond 3rd May 09:00?

[ioncila]

I still don't get it. Just as 24:00 on say 3rd May is the same instant of time as 00:00 on 4th May, your situation suggests to me that 4th May 17:00 is the end of one day and 5th May 08:00 is the start of the next, so in working day terms they are the same instant of time. So I cannot see why 2 days beyond 3rd May 08:00 is not 5th May 08:00. What is 2 days beyond 3rd May 09:00?

Hi
I just saw your post today. So sorry for the late reply.

In fact, I had the same understanding about the terms of a working day. However, I discovered that there were differences when comparing or copying data from MS PROJECT to MS EXCEL.
For the work I do, I use both applications and the part of data developed in EXCEL stems from data created in Project. It was then that I realized that some dates were not the same match.

This was the reason of the posted issue.

Thank you all very much for the help

Cheers
Ioncila