Counting Of Date & Time

[JesusOnEez]

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

 

[Ardus Petus]

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

HTH

 

[Pete_UK]

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]

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