Calculating start / end times based on number of hours, excluding weekends.

Hopefully this will make sense, I have a report with a start time / date, and a number of hours, and have a defined start / end time for a working day. However, my start and end times are causing a problem, and I can't seem to figure out a way around it. The work day starts at 6am, and ends at 2am. If I set the work day to end at 11:59pm, then my formula works fine, as soon as I cross over to 00:00am, then it gives me a #NUM! error.

This is the formula that I am using:

=WORKDAY(P6,CEILING((N6+MOD(P6,1)-U$1)/(U$2-U$1),1)-1)+MOD(P6,1)+(M6+N6)-CEILING(MOD(P6,1)+N6-U$1,U$2-U$1)+U$2-U$1

P6 contains the start time, N6 contains the number of hours, U1 contains the day start, and U2 contains the day end. I assume Excel is getting confused as it is seeing my end time (2am) as before my start time (6am). Can anyone help?

Thanks.

Anyone? I thought I had figured it out, but no luck.

You are probably not getting much help because no one wants to reconstruct your calculations. We don't know what format your day start is or your number of hours, etc. In fact, it's not even clear to me what you are trying to output. You also don't mention what is in M6, which is part of your formula. Recommend uploading your spreadsheet and providing some sample data that passes and fails your expectations.

Pauley

Originally Posted by Pauleyb

You are probably not getting much help because no one wants to reconstruct your calculations. We don't know what format your day start is or your number of hours, etc. In fact, it's not even clear to me what you are trying to output. You also don't mention what is in M6, which is part of your formula. Recommend uploading your spreadsheet and providing some sample data that passes and fails your expectations.

Pauley

Thanks for replying, let me try and explain things a little better. I took out M6, that was a typo, my formula now looks like this:

=WORKDAY(P6,CEILING((N6+MOD(P6,1)-U$1)/(U$2-U$1),1)-1)+MOD(P6,1)+N6-CEILING(MOD(P6,1)+N6-U$1,U$2-U$1)+U$2-U$1

P6 contains the start date / time - Format is 5/4/12 6:00
N6 contains the number of (run) hours - Format is h:mm
U1 contains the day start - Format is time, 6:00:00am
U2 contains the day end - Format is also time, 2:00:00am

I am trying to output into Q6 the completion time, based upon the start date/time in P6 + the number of run hours, this comes from N6. Where my working hours start at 6am, and end at 2am. For example:

Start time is 5/4/12 at 11am
Run hours are 6 hours
End time would be 5/4/12 at 5pm

The next start time (next row) would then be 5/14/12 at 5pm, this piece is working fine.

My problem is that with the end time in U2 set after midnight, I get a #NUM! error. If I set the time before midnight then it works fine.

Hopefully that helps explain a little more what I am trying to achieve, I also uploaded a sample spreadsheet.

sample.xlsx

So what do you want to output if the start time is 5/4/12 1:00 AM and the run time is 6:00? Would that ever happen or would it be 5/4/12 11am or something else?

Pauley

Originally Posted by Pauleyb

So what do you want to output if the start time is 5/4/12 1:00 AM and the run time is 6:00? Would that ever happen or would it be 5/4/12 11am or something else?

Pauley

If that were to happen, then yes I would want the output to be 5/4/12 11am. I think it's rare that situation would occur, but when it does I would want it to be handled like that.

Updated one more time

=B1+B2+IF(OR(AND(OR(B1-INT(B1)<=B4+0.000011574,B1-INT(B1)>=B3),(B1+B2)-INT(B1+B2)>=B3),AND((B1+B2)-INT(B1+B2)>=B4,(B1+B2)-INT(B1+B2)<=B3)),B3-B4,0)

Pauley

As I review this, there are still some unknowns. You want to exclude weekends, but what is your definition of weekend? Is it starting on Friday at midnight? Or Friday or Saturday at 2am (your day end)?

Pauley

Weekends would begin at 2am Saturday morning (so Friday night, depending on which way you look at it).

I just want to say thank you for spending time helping me on this, since yesterday I have already learned various new formula that are available in Excel.

With a quick and dirty weekday adder.

=B1+B2+IF(OR(AND(OR(B1-INT(B1)<=B4+0.000011574,B1-INT(B1)>=B3),(B1+B2)-INT(B1+B2)>=B3),AND((B1+B2)-INT(B1+B2)>=B4,(B1+B2)-INT(B1+B2)<=B3)),B3-B4,0)+IF(WEEKDAY(B1+B2+IF(OR(AND(OR(B1-INT(B1)<=B4+0.000011574,B1-INT(B1)>=B3),(B1+B2)-INT(B1+B2)>=B3),AND((B1+B2)-INT(B1+B2)>=B4,(B1+B2)-INT(B1+B2)<=B3)),B3-B4,0),2)>5,2,0)

Pauley

Thanks, the weekend part is working, but I am experiencing 2 odd issues. The first is that it appears to be ignoring my end time, and the second is that the number of hours being added is acting strange.

I am using the sample that I attached for testing, and used your latest formula. If I put in 2:00 in column A for the run hours, with a start date / time of 5/4/12 6:00am, my end time is calculated as 5/4/12 12:00pm. If I put in 1:00, then the end is 5/4/12 11:00am. I am still trying to figure out each step in the formula and what it does, so not sure which part is causing these issues.

=B1+B2+IF(OR((B1-INT(B1))+B2-B4-0.000011574>1,AND((B1+B2)-INT(B1+B2)>=B4+0.000011574,(B1+B2)-INT(B1+B2)<=B3)),B3-B4,0)+IF(WEEKDAY(B1+B2+IF(OR((B1-INT(B1))+B2-B4-0.000011574>1,AND((B1+B2)-INT(B1+B2)>=B4+0.000011574,(B1+B2)-INT(B1+B2)<=B3)),B3-B4,0)-B4-0.000011574,2)>5,2)

Pauley

Originally Posted by Pauleyb

=B1+B2+IF(OR((B1-INT(B1))+B2-B4-0.000011574>1,AND((B1+B2)-INT(B1+B2)>=B4+0.000011574,(B1+B2)-INT(B1+B2)<=B3)),B3-B4,0)+IF(WEEKDAY(B1+B2+IF(OR((B1-INT(B1))+B2-B4-0.000011574>1,AND((B1+B2)-INT(B1+B2)>=B4+0.000011574,(B1+B2)-INT(B1+B2)<=B3)),B3-B4,0)-B4-0.000011574,2)>5,2)

Pauley

Pauley,Thank you! this one worked perfectly. It might take me another week to understand all the steps (I want to be able to understand how the formula works, instead of simply copying and pasting someone else work), thank you enormously for your effort.

Originally Posted by FDibbins

see if the attached will help you get where you need to be. I developed it for another OP, but it may help you

Thank you, Pauley already had it working. But, I saved your example as I am sure there will be things in there that will be helpful to me in future.