[Solved] Divide time by 2

[sommarbar]

Hi i want to do something as easy as divide a time in to 2 this is my problem:

A1: 04:51
B2: 19:00

I want the time difference betwen them so i did =A1-B2 and it gave me C3: 09:51

Now i want to divide C3 : 09:51 in to half i tried =(A1-B2)/2 but it will not give me right time i want to add half the time of 9 hours 51 minuts
to B2

Ultimately i want C3 to show me the time difference between A1 and B2 and then add half the time of the result to B2 in to Colum C3 witch would be around 00:12 or something

Thank you and sorry for my bad explanation and english

[RoryOF]

use =(B2-A1)/2

The brackets are important.
The cells ought be formatted as Time HH:MM; this worked for me when all cells involved were so formatted.

[Villeroy]

Just in case A1 can greater than B2 as in

A1: 19:00 (from evening)
B2: 04:51 (until next morning)

=(B2-A1+(A1>B2))/2

This adds one day if (A1>B2), so the "negative time" -16:55:30 becomes 14:09:00 which gives 07:04:30 when divided by 2.

[sommarbar]

Hi Thanks for answering, what i released is that when i divide 09:41 with 2 i get 16:55 but i need to get the result 04:50:50 because that is half the time of 09:41. Because in next step i want to add the result 04:50:50 to A1 which would give me time 23:50

[Villeroy]

Format the 09:41 as [HH]:MM and you will see that the true cell value is 33:41:00 (1 day plus 9:41). Half of that value is 16:50:30 indeed.
Preferred number format for a point of time: HH:MM
Preferred number format for a time span: [HH]:MM

[sommarbar]

I see, but i canot add the value 16:50 to 19:00 it wont give me the result i want. It has to be 04:50:30 added to 19:00 wich would give me 23:50

[Villeroy]

You are calculating with wrong numbers. Fix them.
MOD(A1;1) returns the 9 hours beyond 24 hours when A1 equals 33:00.
I can not know if this is what you want because I can not know how wrong cell values got into your spreadsheet nor can I tell what those values actually mean.

[sommarbar]

Thank you for you reply and help, but i dont think you understand what i try to do

Cell A is a time nextday 04:51 Format HH:MM
Cell B is a time today 19:00 Format HH:MM

Cell C i want the time span between Cell B and Cell A Divided in half. For example in this case Cell =A1-B2 the result is 09:51

09:51 is the time span between them, now i want half that time and that is 04:50:50 and this result i want to add to Cell B so the final result in cell C
should be 23:50

The problem is when i divide the result of =A1-B2 (09.41) i get 16:50.50 and not 04:50:50 . If i add 16:50:50 to Cell B 19:00 i get wrong result i dont get 23:50

Thank you very much for trying to help

[RoryOF]

We do understand. What you are not understanding is that you are not dealing with times pure and simple within one day - you are dealing with times that overlap from one day into another and you must decide how to cope with the extra 24 hours involved.

[sommarbar]

Oh okay sorry, how can i do that and still get the result i need

[Villeroy]

If a cell has a value of 33 hours while displaying 9:00, do you want to subtract 24 hours in order to ignore the first 24 hours and calculate with 9 hours? We can not decide this because we do not know what this is all about, if the 33 hours are in that cell for a good reason or by mistake.

[Lupp]

@sommarbar:
You might get some hints from the attached example.

[sommarbar]

Thank you vileroy the extra 24 hours are not there for a good reason i would like to subtract 24 hours then

[Villeroy]

=MOD(A1;1) removes any full days from A1 leaving the remaining time. Apply the formula to all values in question, copy the formula cells and paste-special values over the original cells.

[sommarbar]

@sommarbar:
You might get some hints from the attached example.

Thank you very mutch Exatly what i needed , i added the +1 and worked like a charm thank you for your presantation and help.

Thanks to everybody else who helped very much appreciated Have a nice day