I've just created a spreadsheet to keep track of my attempt to qui
smoking (as from tomorrow). It all works fine, apart from on
section.

I have a cell which shows the amount of "Life Taken Per Cigarette
(which most scientists believe is around 11 minutes for the averag
male smoker). This cell's format is *custom*, and the type is *[mm]*

Another cell counts "Cigarettes Not Smoked", which is a calculate
general number cell.

The cell I'm having problems with is a cell to show "Life Saved". I'
trying to show it in Days:Hours:Minutes, and although I can kind of ge
it working, as soon as the number of days gets to 31, it flips around t
0. I'm hoping to quit smoking for longer than a month or it wouldn't b
a problem.

"Life Saved" cell is calculated as follows;

"Life Taken Per Cigarette" * "Cigarettes Not Smoked".

The format of the cell is *custom*, and the type is *dd "Days" hh:mm*

I realise that putting square brackets around a HH or MM or SS wil
allow for summing over 24 for hours, 60 for minutes and 60 for seconds
and was hoping this would also work for days, so I tried *[DD]* bu
Excel didn't like the formula.

Obviously, I want it to carry on adding the days ad infinitum, not fli
back to zero at the end of a month's worth of days.

Thanks for any help,

Stev

--
JesusOnEe
-----------------------------------------------------------------------
JesusOnEez's Profile: http://www.excelforum.com/member.php...fo&userid=3525
View this thread: http://www.excelforum.com/showthread.php?threadid=55031

Try this:
=TEXT(D29,"0"" days """)&TEXT(D29,"hh:mm")

HTH
--
AP

"JesusOnEez" <(E-Mail Removed)> a
écrit dans le message de news:
(E-Mail Removed)...
>
> I've just created a spreadsheet to keep track of my attempt to quit
> smoking (as from tomorrow). It all works fine, apart from one
> section.
>
> I have a cell which shows the amount of "Life Taken Per Cigarette"
> (which most scientists believe is around 11 minutes for the average
> male smoker). This cell's format is *custom*, and the type is *[mm]*
>
> Another cell counts "Cigarettes Not Smoked", which is a calculated
> general number cell.
>
> The cell I'm having problems with is a cell to show "Life Saved". I'm
> trying to show it in Days:Hours:Minutes, and although I can kind of get
> it working, as soon as the number of days gets to 31, it flips around to
> 0. I'm hoping to quit smoking for longer than a month or it wouldn't be
> a problem.
>
> "Life Saved" cell is calculated as follows;
>
> "Life Taken Per Cigarette" * "Cigarettes Not Smoked".
>
> The format of the cell is *custom*, and the type is *dd "Days" hh:mm*
>
> I realise that putting square brackets around a HH or MM or SS will
> allow for summing over 24 for hours, 60 for minutes and 60 for seconds,
> and was hoping this would also work for days, so I tried *[DD]* but
> Excel didn't like the formula.
>
> Obviously, I want it to carry on adding the days ad infinitum, not flip
> back to zero at the end of a month's worth of days.
>
> Thanks for any help,
>
> Steve
>
>
> --
> JesusOnEez
> ------------------------------------------------------------------------
> JesusOnEez's Profile:
> http://www.excelforum.com/member.php...o&userid=35257
> View this thread: http://www.excelforum.com/showthread...hreadid=550310
>

The square brackets you refer to only work on the time portion of an
Excel date/time value. You might be better off not using Excel's
built-in date/time formats, but rather working directly in integer
minutes. In adjacent cells you can always convert the minutes to
another format via IF statements and the TEXT function.

Good luck with the attempt to quit - I haven't managed it !!

Hope this helps.

Pete

JesusOnEez wrote:
> I've just created a spreadsheet to keep track of my attempt to quit
> smoking (as from tomorrow). It all works fine, apart from one
> section.
>
> I have a cell which shows the amount of "Life Taken Per Cigarette"
> (which most scientists believe is around 11 minutes for the average
> male smoker). This cell's format is *custom*, and the type is *[mm]*
>
> Another cell counts "Cigarettes Not Smoked", which is a calculated
> general number cell.
>
> The cell I'm having problems with is a cell to show "Life Saved". I'm
> trying to show it in Days:Hours:Minutes, and although I can kind of get
> it working, as soon as the number of days gets to 31, it flips around to
> 0. I'm hoping to quit smoking for longer than a month or it wouldn't be
> a problem.
>
> "Life Saved" cell is calculated as follows;
>
> "Life Taken Per Cigarette" * "Cigarettes Not Smoked".
>
> The format of the cell is *custom*, and the type is *dd "Days" hh:mm*
>
> I realise that putting square brackets around a HH or MM or SS will
> allow for summing over 24 for hours, 60 for minutes and 60 for seconds,
> and was hoping this would also work for days, so I tried *[DD]* but
> Excel didn't like the formula.
>
> Obviously, I want it to carry on adding the days ad infinitum, not flip
> back to zero at the end of a month's worth of days.
>
> Thanks for any help,
>
> Steve
>
>
> --
> JesusOnEez
> ------------------------------------------------------------------------
> JesusOnEez's Profile: http://www.excelforum.com/member.php...o&userid=35257
> View this thread: http://www.excelforum.com/showthread...hreadid=550310

Thanks all. I'll give it a whirl after the weekend

--
JesusOnEe
-----------------------------------------------------------------------
JesusOnEez's Profile: http://www.excelforum.com/member.php...fo&userid=3525
View this thread: http://www.excelforum.com/showthread.php?threadid=55031