dividing a time by a whole number to receive a different time

hi,
new to this so forgive me for any newbie errors I make haha

basically im creating a new schedule in excel for diffrerent areas of a steelworks shop floor. I have completed a couple fine but different area required different formats

I need to be able to divide time by a whole number to receive a different time...

e.g. 12:00:00/4=03:00:00

currently the formula is working it out completely differently showing 12:00:00/4=15:00:00

I understand that having it in time format gives it decimal number such as 12:00:00 is 0.5 so I understand that the formula would do 0.5/4=0.125 which is 3 hours

this is not the case in this matter as the result must be 0.625 which is 15:00:00 in time

confused is an understatement, has anyone got any suggestions please?

in the attachment is a screen shot of what im going on about. the cell with the formula shown is what is returning 15:00:00

thanks in advance,
Josh Allen

Attaching a picture of an Excel sheet is not very useful to us, as we can't see the underlying data - it looks like START and END are combined date/time, but we can't see the column identifiers in your snapshot. It would be better if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon does not work.

Hope this helps.

Pete

hi,

it keeps saying upload failed. im guessing this may be due to the size of the file as it has several tabs with a lot of information in it, would it be possible to send this to you via direct email?

thanks
josh

also in response to "special k" would this differ if the 12 hours was the result of another formula?

We don't need the complete file - I did say attach a SAMPLE workbook. Can you just mock one up with a single sheet showing a few sample data items including some that are causing you problems.

Pete

Im hoping that displays all the information properly as i get a lot of information from other tabs, let me know if it doesnt work properly

thanks,

josh

You have:

M8: =$H8*$BH$8

where BH8 is 2:00:00 and H8 is 30, so the result is 60:00:00. This only shows as 12:00:00, though, as the formatting is wrapping at 24 hours. You can see the correct value if you apply a custom format of:

[h]:mm:ss

to cell M8. Similarly:

N8: =M8+($L$2*L8)

is also showing as 12:00:00, but its real value is 60:00:00 (change the formatting on N8 to see this). As you have:

J8: =$N8/$L8

and L8 is 4, then it is correctly showing 15:00:00.

Perhaps you need to consider the logic again, if you expect the result to be 3:00:00 - you could use this instead in J8:

=MOD($N8,1)/$L8

but I don't think that is the correct answer.

Hope this helps.

Pete

spot on mate thankyou, when staring at it for hours on end trying to logically understand it it become rather confusing haha sometimes its simple but takes a 3rd party to point it out haha

yes it was the working out that I was struggling with but it makes a lot more sense now that the duration is 60 hours/4 to get 15 hours overall

thankyou for your help buddy

josh

You're welcome, Josh - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete