• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To find the target dates within the Workdays

D

Deepak D

New Member
Hi all,

I am stuck with one of the formulas to calculate the target date for my job. I work on a 24H schedule.

For example, one of my specific works starts at 11/07/16 8:00 AM and I have a target set for 3 days. So I have written a formula to calculate my target date.
=WORKDAY(Start_Date,3)
Apparently, this formula does not control the time as the return value is 14/07/16 12:00 AM.
I have added a MOD function to correct this time issue (i do not know the concept behind this, however it works well)
=WORKDAY(Start_Date,3) + MOD(Start_Date,1) which gives me 14/07/16 8:00 AM which is correct.

Now my problem starts, if for some reason (non availability) I am putting my work on hold for some days (say 2.5 days i.e 60 hrs) then my target date should shift to the next Monday (18/07/16 8:00 PM).

I have tried several options but I keep getting other values other than the desired result. Sometimes the target date falls on weekends (Saturday and Sunday) or it is showing earlier or it stays at one particular time.

1. WORKDAY(Start_Date,3) + MOD(Start_Date,1) + hold days = target comes on Saturday which is not correct
2. WORKDAY(Start_Date+hold days,3) + MOD(Start_Date,1) = target comes correctly only if the "hold days" is an integer. if it is a fraction then the time doesn't change.

Can anyone help me in this regard.
Waiting for your suggestion on this.

Thanks
Deepak D
 
I'd do something like...
=WORKDAY(Start_Date,3+hold days)+MOD(Start_Date,1)+MOD(hold days,1)

WORKDAY function truncates non integer values (i.e. ignores time, which is stored as decimal value in Excel, which is expressed as MOD(,1)).

EDIT: Time is more accurately expressed as following. x/24 and will always be decimal place. 1 = 24 hours. 1/24 = 1 hour (approx. 0.041666667)
 
Last edited:
Oh, above method may result in target date falling on weekend. To avoid it... you change to following.
=WORKDAY(Start_Date,3+hold days)+MOD(Start_Date,1)+MOD(hold days,1) +CHOOSE(WEEKDAY(WORKDAY(Start_Date,3+hold days)+MOD(Start_Date,1)+MOD(hold days,1) ,2),0,0,0,0,0,2,1)

Basically, it's checking if calculation result falls on Sat or Sun and adds 2 if Sat and 1 if Sun to make it fall on following Monday.
 
Chihiro said:
Oh, above method may result in target date falling on weekend. To avoid it... you change to following.
=WORKDAY(Start_Date,3+hold days)+MOD(Start_Date,1)+MOD(hold days,1) +CHOOSE(WEEKDAY(WORKDAY(Start_Date,3+hold days)+MOD(Start_Date,1)+MOD(hold days,1) ,2),0,0,0,0,0,2,1)

Basically, it's checking if calculation result falls on Sat or Sun and adds 2 if Sat and 1 if Sun to make it fall on following Monday.
Click to expand...

Dear Chihiro,

It worked well.
Thanks a lot.
I really appreciate your help.

Thanks
Deepak
 
You must log in or register to reply here.
Back
Top