Unique ID using date & time

jts2004

Board Regular
Joined
Apr 21, 2004
Messages
156
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Assuming your date and time is in A1 on Sheet1:

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: THANKS

jts2004 said:
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,650
Members
412,335
Latest member
cinciri99
Top