input seconds as numbers and get mm:ss format ?

[meir]

hello,

i need to know if this is possible,i get my raw data (talk time) as number of seconds
and i wanted to know if there is a possibilty to create a vba code that changes
what iv'e writen in the cell to mm:ss format (for example if iv'e enterd 60
in the cell the results for the given cell (after iv'e activated the vba/macro for this
cell only) will show 01:00.
i tried to resolved this issue via conditional formating but didn't succeed.
i know it can be resolved with entering the number to another columm (and than divide it to
(60*60*24)) but i want to do this without another columm.

thanks in advance.
meir

 

[Luke M]

One quick way to write the macro:
Start recording a macro
Type 86400 into a cell somewhere
Copy that cell
Select raw data range.
Paste Special, Divide.
Format cells as desired.
Stop recording.

This will give us the basic architecture. Might be able to use that as is, or you can post it here and we can help simplify it, as needed.

 

[meir]

hey luke,thanks for the quick reply (as always),
iv'e uploaded the file with the recorded macro,
the thing is,i need the macro to make the calculatin and format change only
on the cell that i enterd the number to (meaning that when i activate the macro
it will only change the cell that i'm currently on).

 

[Luke M]

Ah, didn't realize it was just a single cell. The short macro then would be:

Sub FormatCell()
With ActiveCell
    .Value = .Value / 86400
    .NumberFormat = "mm:ss"
End With
End Sub

You might want to combine this idea with a worksheet_change event, if you are doing a lot of data entry.

 

[meir]

you are truly a genius (and a life saver)'
thanks a lot