Formula for Calculating Total Time over 24 hours

[Julie HG]

I'm currently using the formula =(End Time-Start Time+(End Time<Start Time)) which correctly gives me Start Time 16:30 End Time 08:30 = 16:00 hours. I need to corect the formula so that it works out Start Time 16:30 End Time 16:30 = 24:00 total hours, at the moment is shows 00:00. I have separate columns for Start Time and End Time.<START End for another and Time Start column a have I hours. 00:00 of total gives formula above the as hours 16:30 correct need 08:30="16:00" out works correctly which Time)) p Time.<>
Any help would be very much appreciated. Thank you.

 

[Julie HG]

Can't get this message to display correctly, so I'll try again!


I'm using the formula =(End Time-Start Time+(End Time

<START Start total gives the correct which Time)) p Time<>is less than Start Time)) which correctly shows start time
16:30 End Time 08:30 = 16:00 total hours. When it's
Start Time 16:30 and End Time 16:30 = 24 hours total
it shows as 00:00 hours. I'm using separate columns for
Start Time and End Time.<START End and Time Start a I hours. total gives formula the hours 16:30 need 08:30="16:00" correctly which Time)) p separate is that so to or 24 it?s when problem just columns, two in got I?ve adjust more.<>
Any help would be very much appreciated. Thank you.

 

[barry houdini]

Hello Julie,

Mr Excel doesn't like < and > sometimes, try leaving a space at the open end.

For your issue you can add an =, i.e.

= End Time - Start Time + (End Time <= Start Time)

 

[crook_101]

Hi,

If you highlight your cell where your answer is, right click >> Format Cell>> Custom and select hh:mm:ss, you will have to scroll down to get to this. Change the format to this:

[h]:mm:ss

and this will show in the cell as hours....

HTH

Ian,

 

[Julie HG]

Barry and Ian - thank you both very much for you help - it's worked!

Julie

 

[Julie HG]

I've done lots of spreadsheets before and I thought this next bit would be simple!!!! I can't get the correct figure for multiplying total number of hours worked by hourly rate (128:00 hours x £6.00 per hour) I've used formula =SUM(A27*C27) and it's giving me £32.00 when it should be £768.00.

I must be stupid! Thank you if anybody can help me.

 

[barry houdini]

Hello Julie, if the hours are in time format then you need to multiply by 24, try

=A27*C27*24

 

[Julie HG]

Hi Barry,

Thank you so much for your help. I'm not even going to try and understand why that formula works, but it does, thank you!

 

[barry houdini]

In a nutshell, Excel counts in days, not hours so when you have 128:00 in a cell then underlying numeric value of that cell is 5.33 (because 128 hours = 5.33 days - format the cell as number instead of time and you'll see 5.33) so when you multiply that amount by 6 you get 32, you are effectively using a daily rate of £6 rather than an hourly rate.

So multiplying by 24 converts that daily rate to an hourly one

 

[Julie HG]

Barry, simple isn't it, when you know.......! Thank you for spending the time to reply. It must be very rewarding knowing everything about Excel and being able to help idiots like me!!! Many thanks.