Start Shift and an End Shift

I have a Start Shift and an End Shift time,

Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs

I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.

Thank You all that can help.
G

What is wrong with the formula, end shift-start shift?

If you assume A1 is start and A2 is End then difference can be calculated with

=LEFT(A2,FIND("(",A2)-1)-LEFT(A1,FIND("(",A1)-1)

Based on your times that will generate a time value of 5:45:57 ... on that basis I'm not quite sure where you get your .3 from ?

If you want the above in decimal format simply multiply result by 24, ie

=24*(formula)

Which would, based on times, generate the following rounded to 2 decimals: 5.77

First you need to ensure you're subtracting Start Time from End Time ... you presently have some mixed references in your formulae in H re: B/C, ie should read:

Please Login or Register  to view this content.

If your shifts can cross midnight (and do not exceed 24 hours in duration) it would be safer to encase the above in a MOD statement, ie

Please Login or Register  to view this content.

Then to account for decimal breaks etc... simply divide the total of D:E by 1440 (ie 24 * 60)

Please Login or Register  to view this content.

The above is still a time value... ie format to hh:mm:ss

If you wish to convert into decimal hours multiply the whole number by 24

Please Login or Register  to view this content.

Remember to set format of H to be General / Number rather than Time if using the 24* method

EDIT:
Does it matter that the GMT values change - eg C3 vs B3 - do you need to account for the GMT shift in your calcs ? (ie are you at risk of comparing Apples to Pears ?)

It works great, expect GMT time needs to be considered. You are very thoroughly with your codes, this is fantastic! I used the last code.

on which basis - if I've understood - perhaps then:

Please Login or Register  to view this content.

Based on your sample file the above would generate 3.42 & 6.12 respectively as opposed to 3.42 and 5.12 as was the case previously, ie an extra hour is added to the end time in C3 given the GMT is -5 as opposed to -6 as is the case with the start time in B3.

Originally Posted by DonkeyOte

Based on your sample file the above would generate 3.42 & 6.12 respectively as opposed to 3.42 and 5.12 as was the case previously, ie an extra hour is added to the end time in C3 given the GMT is -5 as opposed to -6 as is the case with the start time in B3.

Thats very true, it works great, besides the GMT. If GMT is less then 6 then the figure is incorrect. Do you have any suggestions?

Can you elaborate by means of example(s) ?

Error on my part - simply change the addition of the GMT difference to the end time to be a subtraction:

Please Login or Register  to view this content.

This works ! Wow, I have to say you are an expert at this! Thank You very much. Do you do this as a hobbie, or are you professor? Do you do any of this for cash? Anywho, thank You again. Iam sure I will have more to come.

Good, please remember to mark as SOLVED (see FAQ / How To for guidance if unsure).

Originally Posted by ggovensky

Do you do this as a hobbie, or are you professor? Do you do any of this for cash?

Presently the former. Preferably the latter.

(on an aside please don't use the Quote button to reply - better to use the Post Reply button or worse case trim the Quote down to points pertinent to your reply - else the board becomes a little cluttered).