Excel formula

[James]

Hi,

Can you give me advice - I have manged to this before but cannot remeber
what the formula was. I want to have cell that has a number of 75. Ithen
want another cell to resolve that number 75 but to display as 1.15 ie 1
minute & 15 seconds.

I know its somthing like 24*60*60\the cell
Have tried various combinations but still not working.

If can help thanks in advance.

 

[Bob I]

=INT(A1/60)+(MOD(A1,60)/100)

 

[Keme]

=INT(A1/60)+(MOD(A1,60)/100)

That's the "quick and dirty" way. It does the job of proper displaying,
but when you do calculations based on the resulting cell, you will get
wrong results! (Mathematically: the non-continuous mod function breaks
the linearity, using decimal fractions to represent hexagesimal fractions.)

Use the excel time (clock/date) number format instead. When a time/date
formatted input is recognised, Excel will convert it to a
date-/timevalue (explained below), and format the cell accordingly. The
"time()" function is intended for the kind of conversion the OP requested:
- Assuming the minute value is in cell A1, enter =time(0;A1;0) in the
conversion cell.
- If the time doesn't display correctly, format the cell with a time
format (or custom format, hh:mm for time of day, [h]:mm for accumulating
time.)

*Date-/timevalues*
Excel uses a decimal number for storing time references, with integers
representing days and decimal fractions representing hours, minutes and
seconds (i.e. fractions of a day). Formatting is used to display the
value correctly as time or date/days.

The user must decide whether to interpret the value as a point in time
or an amount of time. (The timevalue 0,5 can mean a total period of
twelve hours, or it can mean twelve o'clock. 0,75 means 18 hours or
6:00pm.)

For advanced time calculations there are a number of functions
available, for combining or extracting days, years, months, weeks,
hours, minutes and seconds.