Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

**zarairebeca** · 05-17-2014, 06:57 PM

Hello,

I found this formula similar to other formula

NETWORKDAYS(D1,F1)-1)*"18:00"-"9:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"9:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"9:00","18:00")

By the way ....

D1 = start Date
F1 = End Date
E1 = start Time
G1 = End Time

It's GREAT! But now I need include time from 6:00 a. m. to 10:00 p. m. (I already modified your formula: (NETWORKDAYS(D1,F1)-1)*("22:00"-"6:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"6:00","22:00"),"22:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"6:00","22:00")) because are regular working hours in Colombia but:

1. All the days (Monday to Saturday) are working days
2. I just want to calculate the time in shifts, I don't specify the start date and end date. Example: 8:00 p.m. to 11:00 p.m. Result: 2 hours, because 1 hour is after 10:00 p.m.

This are two differents options, so I need two separate formulas.

Thank you in advance for your kind cooperation and prompt response whenever possible.

Best regards from Barranquilla, Colombia

**newdoverman** · 05-17-2014, 07:25 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**newdoverman** · 05-17-2014, 07:26 PM

Is this what you are wanting?

**zarairebeca** · 06-30-2014, 06:06 PM

Hello,

I appreciate your answer and suggestion, for that reason I attached an example sheet.

Thank you in advance for your kind cooperation and prompt response whenever possible.

Best regards from Barranquilla, Colombia

Question.xlsx

**newdoverman** · 06-30-2014, 08:47 PM

I am more than a bit confused. Do you really work 16 hours per day 7 days a week?

You have holidays listed but have shifts listed on the holidays. Are these shifts on the holidays actually worked and if so are they handled in a special way?

I didn't use the networkdays function as I don't think that it was appropriate for the data that you have.

Is this in any way close to what you are after?

**zarairebeca** · 07-01-2014, 10:00 AM

Hello,

Thank you so much. I'm going to analyze this formula, It seems that It's working. But later I'm going to explain you further about the worked hours in Colombia.

Best regards

**newdoverman** · 07-01-2014, 10:50 AM

Thank you for the feedback.

When you supply further explanation, please include a workbook with what you expect the results to be.

**zarairebeca** · 07-13-2014, 12:01 PM

Hello,

Question: "Do you really work 16 hours per day 7 days a week?"

Answer: No, in Colombia we work 8 hours per day but depending on the time we get the payment based in an ordinary time (6:00 a.m. - 10:00 p.m.).

Example: If my work hour value $US 10. If I work...

Case 1

6:00 a.m. - 2:00 p.m.
Payment = 8 hours X $10 = $80

Case 2

10:00 p.m. - 6:00 a.m.
Payment = (8 hours X $10) x 1.35 = $80 x 1.35 = $108

I recieve an extra payment(35% = $US 28) because this hours are after the ordinary time (6:00 a.m. - 10:00 p.m.).

Case 3

6:00 p.m. - 2:00 a.m.
Payment = (4 hours X $10) + ((4 hours X $10) x 1.35) = $40 + ($40 x 1.35) = $40 + $54 = $94

So I recieve 4 hours with regular payment (6:00 p.m. - 10:00 p.m.) and 4 hours with extra payment (11:00 p.m. - 2:00 a.m.)

Also I can recieve an extra payment if...

The worked hours in a sunday or holiday day.
I worked more than 8 hours (overtime).

This link explain more (spanish)
http://www.gerencie.com/horas-extras...-festivos.html

**zarairebeca** · 07-13-2014, 12:05 PM

Hello,

It's pending formula that allows me:

I just want to calculate the time in shifts, I don't specify the start date and end date. In other words, I need the same formula (It's great and work fine, thank you so much) but excluding the date.

NOW
Start Date Start Time End Date End Time Total Hrs Worked Hrs After Worked Hrs.
16-abr-14 06:00 p. m. 17-abr-14 02:00 a. m. 8 4 4

NEW REQUEST
Start Time End Time Total Hrs Worked Hrs After Worked Hrs.
06:00 p. m. 02:00 a. m. 8 4 4

Best regards from Barranquilla, Colombia

**newdoverman** · 07-13-2014, 02:46 PM

Thank you for the explanation.

For the question asked:
If the End Of Day time is entered in A1, Start time entered in A2 and End time is in B2:

Total time entered in C2: 8 Hours

=A1-A2+B2-A1+(A1>B2) formatted as [h]:mm

For breakdown of the 8 hours: 4 and 4:

In C2: =A1-A2 then in D2: =B2-A1+(A1>B2) formatted as [h]:mm

Change the references to where you have the times entered.

Hope this helps.

**zarairebeca** · 07-13-2014, 11:09 PM

Hello,

I appreciate your answer but this time (as you suggest) I included a workbook with what I expect the results to be. Sorry if I made you confused.

You can see that is the same formula (Question - ndm.xlsx) without the start date and end date.

Best regards from Barranquilla, Colombia

Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

LinkBack

Thread Tools

Rate This Thread

Display

Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Re: Calculate work time in range time (6:00 a. m. to 10:00 p. m.)

Thread Information

Users Browsing this Thread

Similar Threads

How to calculate time worked: Start time, end time, lunch break

Calculate if Time range appears in an other Time range

Calculate how much work employees can get done in a certain time

time:how to work out how to calculate my finish time

Bookmarks

Bookmarks

Posting Permissions