I can't get my head round this! Please help!

[lukeflegg]

Hello,

I've scoured the web and can't find any solution that works or makes sense to me:

I'm trying to work out how many hours I've worked each day from this:

START FINISH DAY TOTAL
0930 1715

I want day total to be the amount of hours i worked that day.
Is there any way of doing this??

Thanks so much :]

 

[shg]

      -A- -B-- -C- -------------------------------D--------------------------------
  1   930 1715 745 C1: =DOLLARFR(DOLLARDE(B1/100,60) - DOLLARDE(A1/100,60), 60)*100

You're making a rod for your back by not entering convential times with a colon .

 

[lukeflegg]

Thanks so much for your help -I'm a total novice with Excel.

http://i204.photobucket.com/albums/bb7/fireandfeathers2/excel12th1451.jpg

that's all I'm getting!

Do you mean it'd be easier if it was formatted like
09:30
instead of
0930
?

I don't mind about how it's laid out, I can change it, I just don't want to enter thousands of columns of data again by hand!

 

[West Man]

Try =TIME(LEFT(B1,2),RIGHT(B1,2),0)-TIME(LEFT(A1,2),RIGHT(A1,2),0) and format as hh:mm

 

[lukeflegg]

Am I doing something really stupid? it's not working...

http://i204.photobucket.com/albums/bb7/fireandfeathers2/excel12th1536.jpg

 

[West Man]

Your example showed 0930 as the start and your actual data is 930. If all start times are 3 digits then try: =TIME(LEFT(B1,2),RIGHT(B1,2),0)-TIME(LEFT(A1,1),RIGHT(A1,2),0)

If they are a mixture try: =TIME(LEFT(B1,LEN(B1)-2),RIGHT(B1,2),0)-TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)

 

[HalfAce]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--> Here's a couple things you might try:
Start time in A1, End time in B1.
Your formula (in C1? - Formatted as Custom & [h]:mm):

=( B 1 + ( B 1 < A 1 ) - A 1 )

Now, for the format used on entry, by using an extra 2 columns you can use (say column A) for the actual entry and column B for your return. Enter this formula in B1 (& copy down):

=TEXT(A1,"00\:00")+0

Format this cell as Time & 1:30 PM
With that you can enter your time as 0930 in A1 and have it return 9:30 AM in B1. (or enter 1425 and get 2:25 PM, etc.)

Hope it helps.

EDIT:
I don't know why that first formula won't post normally so I added a space between each character...

 

[lukeflegg]

Hurrah!

That did it :]

=MOD(TEXT(C3,"00\:00")-TEXT(B3,"00\:00"),1)

and the DAY TOTALS add up bueatifully.
Thank you so much

 

[HalfAce]

Most welcome. Glad it helped.