• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

input seconds as numbers and get mm:ss format ?


New Member

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.
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.
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).


  • macro.xlsm
    17.5 KB · Views: 1
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.