Unique ID using date & time

[jts2004]

I have a file save as macro that looks at a value in a cell that I create. I want that value to be YEARMONTHDAY MINUTES.

EX: 20051220 719 which means 12/20/2005 11:59AM

the problem in using the now() function is that you can't use "/" or ":" when saving a file. so the cell value has to equal "20051220 719" in order for the macro to save the file.

 

[Andrew Poulsom]

Assuming your date and time is in A1 on Sheet1:

Sub Test()
    With Worksheets("Sheet1").Range("A1")
        MsgBox Format(.Value, "yyyymmdd") & " " & WorksheetFunction.Round((.Value - Int(.Value)) * 1440, 0)
    End With
End Sub

 

[jts2004]

THANKS

Any way to do it with a formula in A1

 

[Andrew Poulsom]

Re: THANKS

Any way to do it with a formula in A1

A formula in A1 referring to what? If the date and time were in B1:

=TEXT(B1,"yyyymmdd")&" "&ROUND((B1-INT(B1))*1440,0)

 

[jts2004]

thanks

thats what I need