Setting work time at Excel

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Hi ,

Anyone can help me on below problem's,,.,I did attached examples below..

<table x:str="" style="border-collapse: collapse; width: 479pt;" border="0" cellpadding="0" cellspacing="0" width="638"><col style="width: 99pt;" width="132"> <col style="width: 59pt;" width="78"> <col style="width: 96pt;" width="128"> <col style="width: 114pt;" width="152"> <col style="width: 111pt;" width="148"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 99pt;" height="18" width="132">My working time</td> <td style="width: 59pt;" width="78">7am to 7pm</td> <td style="width: 96pt;" width="128">12 hrs</td> <td style="width: 114pt;" width="152">
</td> <td style="width: 111pt;" width="148">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 40.5pt;" height="54"> <td class="xl28" style="height: 40.5pt; width: 99pt;" height="54" width="132">Date Start</td> <td class="xl29" style="width: 59pt;" width="78">Time Work</td> <td class="xl29" style="width: 96pt;" width="128">Days work (Time work Divide 24)</td> <td class="xl29" style="width: 114pt;" width="152">Date stop (Date Start + Days Work)</td> <td class="xl29" style="width: 111pt;" width="148">Result</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl26" x:num="">12</td> <td class="xl27" x:num="0.5" x:fmla="=B4/24">0.50 </td> <td class="xl25" x:num="40408.791666666664" x:fmla="=A4+C4">8/18/10 7:00 PM</td> <td>O.K</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl26" x:num="">13</td> <td class="xl27" x:num="0.54166666666666663" x:fmla="=B5/24">0.54 </td> <td class="xl25" x:num="40408.833333333328" x:fmla="=A5+C5">8/18/10 8:00 PM</td> <td>Getting wrong Result</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl26" x:num="">13</td> <td class="xl27" x:num="0.54166666666666663" x:fmla="=B6/24">0.54 </td> <td class="xl25" x:num="40409.333333333336">8/19/10 8:00 AM</td> <td>Expecting Result</td> </tr> </tbody></table>
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,667
Say you have the normal Starting Time (7:00 AM) in D5 and the normal Ending Time (7:00 PM) in E5. Have your starting DateStamp (=date + time) in D9, working hours in E9 and the "Days Work" -formula (=E9/12) in F9.

Then the formula to return the desired Date Stop would be:

=IF((D9+F9)>(D9-D5+E5),(D9+1)+(F9-(E5-D5)),D9+F9)

ie IF((starting time + working hours)>(Starting Date at 7 PM),(the next day + (working hours - 12), starting time + working hours)

The formula is not perfect but works with your examples.
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Really appreciate for ur help...You manage to help me on what i want ..
This formula work great but for more than 25 hrs it's getting wrong result..Attached the example..for my application
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
sorry ,

Really appreciate for ur help...You manage to help me on what i want ..
This formula work great but for more than 25 hrs it's getting wrong result..Attached the example..for my application the "days work" it's not fixed it's can go up to 100 hrs , this value is sum from other cell.
Attached the example

<table x:str="" style="border-collapse: collapse; width: 474pt;" border="0" cellpadding="0" cellspacing="0" width="630"><col style="width: 100pt;" width="133"> <col style="width: 82pt;" width="109"> <col style="width: 64pt;" width="85"> <col style="width: 100pt;" width="133"> <col style="width: 128pt;" width="170"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 100pt;" height="18" width="133">DateStamp</td> <td class="xl25" style="width: 82pt;" width="109">working hours</td> <td class="xl25" style="width: 64pt;" width="85">day work</td> <td class="xl24" style="width: 100pt;" width="133">Date Stop</td> <td class="xl25" style="width: 128pt;" width="170">Result</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">13</td> <td class="xl27" x:num="0.54166666666666663" x:fmla="=B2/24">0.54 </td> <td class="xl26" x:num="40409.333333333328">8/19/10 8:00 AM</td> <td class="xl25">Getting Result--Correct</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">24</td> <td class="xl27" x:num="1" x:fmla="=B3/24">1.00 </td> <td class="xl26" x:num="40409.791666666664">8/19/10 7:00 PM</td> <td class="xl25">Getting Result--Correct</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">25</td> <td class="xl27" x:num="1.0416666666666667" x:fmla="=B4/24">1.04 </td> <td class="xl26" x:num="40409.833333333328">8/19/10 8:00 PM</td> <td class="xl25">Getting Result--Wrong</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">25</td> <td class="xl27" x:num="1.0416666666666667" x:fmla="=B5/24">1.04 </td> <td class="xl26" x:num="40410.333333333336">8/20/10 8:00 AM</td> <td class="xl25">Expecting result</td> </tr> </tbody></table>
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,667

ADVERTISEMENT

With the same data layout try:

=IF((D9+F9)>(D9-D5+E5),(D9+ROUNDDOWN(E9/(E5-D5)/24;0)+(($E$9/24)-ROUNDDOWN(E9/24;0))),D9+F9)

Basically the same function as before but the +1 day part has been replaced with "total working hours / hours in a normal workday".

It's still not perfect (= what if the person showed up earlier than expected?) but should work with the given data.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Are you excluding any weekdays from your calculations ? (eg Sat-Sun)

Below is a variation on WORKDAY based calculation which uses only working hours:


Excel Workbook
ABCD
1DateStampworking hoursday workDate Stop
218/08/2010 07:001319/08/2010 08:00
318/08/2010 07:002419/08/2010 19:00
418/08/2010 07:002520/08/2010 08:00
518/08/2010 07:002520/08/2010 08:00
Sheet3


It does assume however that start date time is within a valid working window.

Prior to XL2007 the use of WORKDAY function requires activation of the Analysis ToolPak Add-In (Tools -> Addins)
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131

ADVERTISEMENT

Really thanks a lot for both of u...both formula work for me but the most suitable is the below one..



=WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-"07:00")/("19:00"-"07:00"),1)-1)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-"07:00","19:00"-"07:00")+"19:00"-"07:00"

Again thanks a lot...

May god bless u all.
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Hi DonkeyOte,

Can u pls provide me again the formula but don't excluded saturday & Sunday...I have another formula to exclude this cause not all the saturday is my offday..I attached again the formula just do some correction to included saturday & Sunday in formula..


WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-"07:00")/("19:00"-"07:00"),1)-1)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-"07:00","19:00"-"07:00")+"19:00"-"07:00"
 

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Sorry , maybe it's not so clear for u..Pls don't include saturday & sunday inside the formula calculation , i have another formula to exclude this....

Just provide me formula with included saturday & sunday inside calculation , later i use another formula to exclude this since not all my saturday is offday.

=WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-"07:00")/("19:00"-"07:00"),1)-1)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-"07:00","19:00"-"07:00")+"19:00"-"07:00"
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I have seen your other thread on this topic and to be honest I'm not sure you've quite thought this through in full - but to answer your question:


Excel Workbook
ABCDEFGH
1DateStampworking hoursday workWEEKDAYANY DAYStartEnd
218/08/2010 07:001319/08/2010 08:0019/08/2010 08:0007:0019:00
318/08/2010 07:002419/08/2010 19:0019/08/2010 19:00
418/08/2010 07:002520/08/2010 08:0020/08/2010 08:00
519/08/2010 07:003724/08/2010 08:0022/08/2010 08:00
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,072
Latest member
2020914

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top