Format Cells

[aftabn10]

Hi, I am looking for some asisstance in regards to the following calculations that im working on.

I currently run the following formula in C2:

(Vol * Time)/86400

which converts the results into a time format, the problem i am having is that when i have done this for 200 cells, i then copy and repaste the results as values but some results show with a date in front of the time and some dont and also these dates vary. (the following are some of the results i am getting)

00/01/1900 00:16:48
01/01/1900 16:57:56
00/01/1900 21:32:00
00/01/1900 08:24:20
26/08/1900 07:01:24
02/01/1900 20:20:06

So my question is, how can i fix the cells to stay as just hh:mm:ss without the date in front, as i have also tried to run the text to columns function to separate the date and time, but what this does is that if that dates vary it adds an extra 24 hours to the time, which does not give me the correct result.

If somebody could please help, would really appreciate it, thanks in advance.

[peterjuhnke]

Why don't you use the "Right" function.
Lets assume that the first eg is in "C2" and the rest are below, right the fomula in "D2" and drag the formula down, or copy and special paste "formulas"
eg
00/01/1900 00:16:48 =right(c2,8)
01/01/1900 16:57:56 =right(c3,8)
00/01/1900 21:32:00 =right(c4,8)
00/01/1900 08:24:20 =right(c5,8)
26/08/1900 07:01:24 =right(c6,8)
02/01/1900 20:20:06 =right(c7,8)

Hope it's of use

Regards
Yonks

[aftabn10]

Thanks for your response Peter. I have tried this on the following value which is in cell D4:

00/01/1900 00:06:06

and the formula in E4 =RIGHT(D2,8) gives me a result of 11111111 even though the format is set as hh:mm:ss.

What am i doing wrong? Thanks once again.

[aftabn10]

Is their anyone who can help me with this?

[shg]

Format the result as [h]:mm:ss or [hh]:mm:ss

[aftabn10]

Thanks for your response shg. I have tried to change the format but nothing changes, it still shows as 11111111 even with the formatting set as [hh]:mm:ss or [h]:mm:ss. Any other methods to get this resolved?

[martindwilson]

00/01/1900 00:06:06
what formula gives that result?
there is no month/day 00
however
01/01/1900 16:57:56
formated
[hh]:mm:ss
gives
40:57:56

[aftabn10]

Thanks for your response martindwilson. As explained in my first post, I am using this formula:

(Vol * Time)/86400

and the results show some figures with date in front and some dont. So in order to use text to columns i formatted the complete results to give all results a data in front, this is when i see this date in front of the results that had no dates in front initially. The main problem being is that i have some results that are over 24 hours e.g. 120 hours etc and when i use text to columns this does not work properly. For example:

05/01/1900 122:18:40

the above when split shows up as:

05/01/1900 120:00:00 | 02:18:40

What i want to do is just show:

122:18:40

without the date. Any ideas? Hope that made sense.

[shg]

there is no month/day 00

Sure there is, Martin -- a value between 0 and 1 is in 0 Jan 1900. Some disciplines (calendar people? astronomers?) commonly use the 0th day of month.

aftabn, we could get this resolved in about 10ms if you'd post a workbook that shows what's broken.

[martindwilson]

well i never! knock me down with a feather!
but
00/01/1900 00:06:06
will not resolve to a time
it gets treated as text

[aftabn10]

shg and martin thanks for your responses. I have attached a sample workbook of some of my results. Currently the figures in Col D have not been broken into values but i have copied this into column E and used text to columns which is where it goes wrong.

Thanks once again for your help.

[shg]

This is what I see:

Please Login or Register  to view this content.

What's the problem?

[aftabn10]

the problem is that i need to split the date and time, and have the time value on its own as this will be used in a calculation.

[shg]

There is no date, per se, in an elapsed time, right? If you work on something for three days, what's the date when you finish? It depends on when you start ...

In any case, the whole number part is =int(a1) and the fraction part is =mod(a1, 1)

Format the first formula as a date (or Number) and the second as time.

[aftabn10]

shg, i have put these in the sample workbook but still does not give me the correct figures. I have attached the workbook again.

[shg]

What you have is correct. What's the question?

[martindwilson]

how does 388*1 = 0.004490741 ?

[aftabn10]

the calculation is (388*1)/86400 to give me a figure in hh:mm:ss. hence it = 0.004490741.

[aftabn10]

the question is that the split does not work properly as for example column d4 shows as 05/01/1900 02:18:40, so what i would like to do is split this so that e4 is showing 05/01/1900 (or should be 00/01/1900, in order to split the time in f4) and f4 is just showing 122:18:40.

D4 | E4 | F4
05/01/1900 122:18:40 | 05/01/1900 (or 00/01/1900) | 122:18:40

Hope that makes sense.

[martindwilson]

but the time value is on it's own already!

[Cheeky Charlie]

I'm not sure if this is clear:

Excel stores dates and times as numbers, as far as Excel is concerned, "6th June" is just a mask (format) you put on a number to make it easy for people to understand. In order to keep things simple, Excel considers each day to be worth 1 and for *all time* to start on the 1st January 1900. Don't ask me why the people at Microsoft hate the 19th Century.

Logically, Excel then considers parts of the day to be times, so 1 day + 0.75 days = 6pm tomorrow evening.

With this understanding, you can apply all sorts of manipulations to dates and times, and simply format the cells (with date, or time, or both etc.) so that the output makes sense to you as a human.

One last note to add, you can use [h]:mm or [m]:ss or similar to force Excel to "accumulate" the [bracketed] measure, for example 2 days, 3 hours, 12 minutes, 23 seconds is 3:12:23 formatted as h:mm:ss but 51:12:23 formatted as [h]:mm:ss - significantly, the underlying number is still the same.

HTH