Hello, I need help trying to convert my time in excel on a timecard. Can you please help me? I came in at 9:15am and I left at 4:30pm. Thanks in advance.

You should try to be a little more specific in your posts. Convert your time to what? Hours? Pay? % of Day? If you are trying to see how many hours you were clocked in for, try: A1 contains 9:15 AM B1 contains 4:30 PM C1 contains =(B1-A1)*24 and should show 7.25

Im just want to understand how you get this answer if you dont mind can you break it down and explain to me how you get this answer? - Why do you subtract A1 from B1? - Why do you multiply by 24? - If I had to do this without excel how would I get this answer?

Hi, Here is some information on Time in Excel. Times in Excel appear difficult, but if you ensure that all data entry follows a time format that Excel recognizes, you will be able to add and subtract times without any need to use any special calculations. Enter start and finish times as hh:mm, for example 07:30 Format all the cells used for time entry and time calculations with "hh:mm" Use cell data entry validation to restrict entries to times (Toolbar - Data - Validation and select Time) The duration of work is just Finish-Start Start time in A2 07:30 Finish time in B2 11:10 Duration in C2 has the formula =B2-A2 and if formatted "hh:mm" will show 3:40, i.e., 3 hours and 40 minutes If staff may work past midnight use the following formula =IF(B2<A2,B2+1-A2,B2-A2) This adds 1 to the finish time when the finish time is 'earlier' than the start time, because the finish time is in the next day. Excel's date/time serial number holds dates in the integer (whole number) part of the serial number and times in the decimal part of the number. For example 09 September 2009 is 40066 12 noon on 09 September 2009 will be 40066.5 i.e. half way through the day. If you need to calculate pay you will have to convert hours and minutes to fractions or percentage of a day, for daily rates or convert minutes to fractions of an hour for hourly rates. Only do your decimal conversion for payroll calculation as a final step - don't mix decimal calculations and Excel's time serial numbers, i.e., calculate time differences using Excel's time system, then do the conversion on the result, don't try and convert the start and finish times separately. HTH Regards

To expand on Humar's response and answer your question Why do you multiply by 24?: Humar said: "12 noon on 09 September 2009 will be 40066.5 i.e. half way through the day." It's the .5 that says you are half way through the day. Enter 9:15 AM in a cell and format it as a number. You'll get 0.385416666666667, since you are ~39% though a 24 hour day. Enter 4:30 PM and the resulting decimal number is 0.6875 or 68.75% through the day. Subtract these values and you get: 0.6875 - 0.385416666666667 = 0.302083333333333 0.302083333333333 x 24 hours = 7.25 hours re: If I had to do this without excel how would I get this answer? Use your fingers.

Thanks. I gave my sorry supervisor my timecard with 7.25hrs. before I posted my question and she said it was incorrect. I jus wanted to be sure that I was correct before I told her she was wrong. I had to explain to her how I did it on excel, I thought I did it wrong on excel. Thank You Much!!! P.S. They just hired anyone now a days. [sigh]

Hi, You asked about manual calculation: Manual calculation of time difference and display as decimal 1. Convert times to 24 hour clock 2. Subtract minutes first e.g. 10:15 -08:14 3. If end minutes less than start minutes - just subtract 15-14=1 then subtract hours Result: 2hours and 1 minute 4. If end minutes greater than start minutes e.g., 10:15 -08:20 Add 60 to start minutes, then subtract: 15+60 =75 75-20=55 Add 1 to start hour, then subtract from end hour (getting back the 60 minutes we just borrowed) 08+1=9 10-9=1 Result: 1 hour 55 minutes 5. Convert minutes to fraction of an hour: 1/60 = 0.0166666 (recurring) 55/60 = 0.9166666 (recurring) 6. Round result to required number of decimal places Result: 2.02 hours Result: 1.92 hours Just to check our calculations: 7. The difference is 1.92-2.02 = 0.1 8. Convert decimal part of hour to minutes 60*0.1 = 6 minutes i.e., the difference in our two example starting times 08:20 - 08:14 = 6 minutes Regards