Time Calculations

[Jsimpson19]

Good Evening. I am new to this forum. I hope someone can help. i have attached my spreadsheet i have been working on but what i would like to do is not have to enter the colon in when entering the start time and end time.

I do have hidden formulas in the row below what you can see in each square. What I have so far has taken me 3 nights of searching the web but i cant seem to find anything on auto adding the colon with the way i have my sheet set up

thanks in advance for the help

[tgsekhar]

You can Use Custom Format 0\:00 for the purpose. Following URL provides use of different custom formats. Hope this helps your case.
http://www.pcmag.com/article2/0,2817,2316755,00.asp

[Toonies]

try formatting your cells to this

Please Login or Register  to view this content.

[Toonies]

this may help you

http://lacher.com/examples/lacher34.htm

[Toonies]

you could check this out

http://www.excelforum.com/excel-gene...me-format.html

[Kevin UK]

Hi Jsimpson19

If you have a lot of times to enter, you could use the Auto Correct Options with may be Replace: ";" With: ":"
But remember to change it back!

[Fotis1991]

Ηι

Typing time as number(example:12:50AM=2450---12:10pm=1210) in A6 & C6, try this formula, E6.

=(HOUR(TIME(VALUE(LEFT(C6;LEN(C6)-2));VALUE(RIGHT(C6;2));0)-TIME(VALUE(LEFT(A6;LEN(A6)-2));VALUE(RIGHT(A6;2));0))+MINUTE(TIME(VALUE(LEFT(C6;LEN(C6)-2));VALUE(RIGHT(C6;2));0)-TIME(VALUE(LEFT(A6;LEN(A6)-2));VALUE(RIGHT(A6;2));0))/60)*C3

** Format columns A-C-E as general

** Change all semi colons in my formula to comma.

[Kevin UK]

Hi Jsimpson19

Seeing everyone is offering formulas! See the attached sheet, The start times & finishes times are formatted as "0000". So for time 07:30, enter as 0730. Then in the total hours cell:

Please Login or Register  to view this content.

The result is a decimal number, rounded to 2 decimal places. If you want the result as proper time, remove the ROUND function and the "*24".

[Jsimpson19]

Worked perfectly. Thanks so much

[popipipo]

If you have a lot of times to enter, you could use the Auto Correct Options with may be Replace: ";" With: ":"
But remember to change it back!

I use this:Replace: "++" With: ":"
And don't change it back and it works in all the files I work with

[Kevin UK]

Hi Jsimpson19

No problem and thanks for the feed back.