Date & Times

[zircom]

I need help in getting a total time from date of entry (a job opened) to completion (job closed) in hours/mins, and taking into account weekends/holidays.

COL A COL B COL C
Date Time Date
Opened Opened Closed
02/05/2003 13:16 03/05/2003

COL D
Time
Closed
15:11

 

[Bowers74]

1. Format your result cell using custom formatting and enter [hh]:mm as the format.

2. Enter the following formula in Column your result cell:

=(C2+D2)-(A2+B2)

This formula is assuming that your result cell will be E2.

Enjoy!

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[bluedragon2]

Just playing around with this task, but try this:

I assumed 8 hours per working day:

=INT((NETWORKDAYS(A1,C1,37669)*8)+(D1-B1))&":"&MINUTE(MID((NETWORKDAYS(A1,C1,37669)*8)+(D1-B1),FIND(".",(NETWORKDAYS(A1,C1,37669)*8)+(D1-B1)),8))

If you want 24 hours per working day, change the 8 in the NETWORKDAYS formulas to 24.

I also added presidents day as the only holiday in the formula because that was the only holiday in that time period.

To add the rest of the hollidays, Change each NETWORKDAYS formula. look at excel help for the format.

Blue

 

[bluedragon2]

The cell that you enter the formula in needs to be formatted as general.

Blue

 

[zircom]

thanks for the advice, that seems to work.

How would i get it to calculate working day from 08:30 - 17:30 per day (not including weekends).

And will it calculate correctly if it is the same day?

Thanks once again

 

[bluedragon2]

I made some changes to the formula, it wasn't handleing minutes right. The same day was an issue with the networkdays formula also.

Try this for 8:30-17:30 days and execute for same day:

=IF(A1=C1,(HOUR(D1-B1))&":"&MINUTE(D1-B1),(NETWORKDAYS(A1+1,C1,37669)-1)*9+(HOUR((0.72916667-B1)+(D1-0.354166666)))&":"&MINUTE((0.72916667-B1)+(D1-0.354166666)))

Remember, I only put in presidents day in the networkd days formula.

Blue

 

[zircom]

thanks again, that wrked fine.

I am now going to try and get it to look at different scedules for contract types to see if i can get it to calculate a customer that has a: different working hours and days. THis will bew based on the contract ie:

Customer 1 = M-F 08:30 - 17:30

Customer 2 = M-S 08:30 - 17:30

Customer 3 = M-F 24 hours

Etc

Thanks again

 

[zircom]

Hi,

Sorry on closer reading it is not working correctly.

Start Date St Time End Date End Time Calc
02/05/2003 10:45 02/05/2003 15:11 4:15

And this seems the case throughout.

Any ideas

 

[bluedragon2]

Strange, I am getting 4:26 on mine.

 

[bluedragon2]

Do you have the two time cells set as time?

And, do you have the calc cell set to general?

 

[zircom]

Hi,

everything is set to Time or General.

The time difference should be 4:26, but I still get 4:15.

=IF(B3=K3,(HOUR(L3-C3))&":"&MINUTE(D3-C3),(networkdays(B3+1,K3,37669)-1)*9+(HOUR((0.72916667-C3)+(L3-0.354166666)))&":"&MINUTE((0.72916667-C3)+(L3-0.354166666)))

I have only changed the col's to fit the spread sheet.

Any clues.

 

[bluedragon2]

You have:

=IF(B3=K3,(HOUR(L3-C3))&":"&MINUTE(D3-C3)

Should it be:

=IF(B3=K3,(HOUR(L3-C3))&":"&MINUTE(L3-C3)

 

[zircom]

great that works, stupid mistake, thanks again