formula for "date created" [SOLVED]

  • I have a worksheet used as a purchase order. I would like to have a cell display the date that the purchase order was created. I know about the =now() formula, but that date then changes the next day to the current date. I want to have the date that I actually created the PO. I know this must be a simple formula, but I just can't find it in the "help" of excel. Any help is greatly appreciated in advance.
    Thanks-Larry

  • Code
    Private Sub Workbook_Open()
    Worksheets("Sheet1").Range("A2").Value = Date
    End Sub


    Place the code in the module ThisWorkbook
    under the workbook open event


    See attached file ... to view the code, open workbook & hit Alt+F11 - code is on This workbook...


    :) hth

  • As an example of what I am trying to do...in an older MS Works spreadsheet program, there was a function "=printdate", which would do the trick for me, since these PO's are always printed via fax, therefore the date I created the PO was recorded.
    Thanks-Larry

  • Code
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Worksheets("Sheet1").Range("A3").Value = Date
    End Sub


    Will place the date in a cell of your choice (just change Cell "A3" for whatever you need) immediately prior to printing the Purchase Order - Or, you could just pop the code into a print macro if that's what you're using...


    HTH :guitar:

  • It sounds like this should do the job for you;


    Sub AddDate()
    Dim ThisDate As Date
    ThisDate = Date
    'check to see if range is empty


    If IsEmpty(Range("A1")) = True Then
    'if the range is empty place the current date in range
    Range("A1").Value = ThisDate


    ElseIf IsDate(Range("A1")) = True Then
    'if a date is already present in range do nothing
    Exit Sub

    End If




    End Sub



    HTH


    Bruce

  • I am not using a macro. OK I got the drift of the code you posted, but don't have a clue as to where to put that code to gain the result I am seeking. I did get the

    Quote

    (just change Cell "A3" for whatever you need

    but thats all I got. Sorry for being such a dummie
    thanks-Larry

  • Open your workbook


    Press Alt+F11 (alt Key and F11 key together)


    This should bring up the Visual Basic Editor (VBE) window


    On the Left hand side of the window, you should have a pane titled "Project - VBA Project"


    This will list all open workbooks


    Expand the project for your workbooks
    You should get a listing of all your workbook sheet names, along with an option called "This Workbook"


    Double click on this workbook
    You should get a white screen at the top of which you'll have 2 boxes "General" & "Declarations"


    Select general & "This WOrkbook"


    Select Declarations and say "Before Print"


    Paste the code here


    Hope this helps - post back if it still doesn't


    :spin:

  • Here's what I have done with this formula:
    I place the formula in cell i2
    this exact formula:
    =IF(ISBLANK(F2),NOW(),"")
    cell F2 being where I want my result...I get no results in F2. I must be missing a step.________________________________



    Using the VBE, I get to the window with "General" on upper left & select "Declarations" on the upper right ( I only have this one workbook open...so I do not see a selection for "Open this Workbook), then pasted the code you posted, return to worksheet, & print the worksheet. Still do not have the print date in cell F2. Below is a cut & paste of VBE

    Either way I try I do not get the results in cell F2...what next...I like the formula route best, but would settle for anything that gets the result.
    thanx so much for your input-Larry


    edit BTW: I have renamed the "Sheet 1" to "Sagebrush White" which is the name of that sheet, and then replaced "Sheet 1" with "Sagebrush White" in the VBE code. but still no results.

  • I have tried the three different methods suggested, and here are my results:
    I copy the code on post, and paste the code into Microsoft Visual Basic (Alt+F11) selecting (declarations). It creates a category “Before_Print”. Here is a cut & paste of the code pasted:

    Then I return to workbook, and print it.
    Results: does not return result in “F2”
    ………………………………………………………………………………………………
    Then I tried the next formula suggested, cut & paste the same way.


    Still no result in F2
    ……………………………………………………………………………………………
    Then I tried the formula that bnix suggested. I pasted this formula exactly like this =IF(ISBLANK(F2),NOW(),"") into cell I2
    I still do not get any result in F2
    ………………………………………………………………………………………
    I have no idea why one of these procedures won’t return a result. Perhaps another approach such as a “date created” or “date last saved”. This shouldn’t be that hard to achieve. Is it possibe that there is an add-on that I do not have installed, that is causing the "no result". I am running Excel 2002.
    Thanx-Larry

  • Hi


    Let's add another way of getting the creation time of a file: ;)


    In a code module (VBE Editor Insert | Module), add the following code:

    Code
    Function FileCreated() As Date
        FileCreated = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
    End Function


    The function returns the creation date of the active workbook.


    HTH
    /Fredrik

  • Have a look at the file I attached earlier in the thread.... It does work buddy so therefore I can only assume you have not pasted the code in the correct place.


    :no:


    my instructions read...


    Quote

    Select general & "This WOrkbook"


    Select Declarations and say "Before Print"


    and not



    Quote

    I copy the code on post, and paste the code into Microsoft Visual Basic (Alt+F11) selecting (declarations)


    You need to select "This Wokbook" from the option box that has "(General)" in it and "Before Print" from the option box tha has "(Declarations)" in it


    hth :cheers:

  • Quote

    Select general & "This WOrkbook"


    Select Declarations and say "Before Print"

    Ater opening my worksheet I hit Alt+F11, which opens up Microsoft Visual Basic screen. In the payne, there is General on the left. I hit the drop down box..and there is NO option for "This WOrkbook". The only option is "general"...no other options available (I previously though that was because I only have one workbook open). So I then go to the right, and select (Declarations)...paste the code into the payne. Go back to the workbook, & print.
    Still no results in the target cell.
    ________________________________
    FWind, when I do your sugestion, I first copy the code from your post, then open the workbook, Alt+F11 to bring up the editor, then select "insert" from the top top, select "module" paste the code. I return to the workbook, but do not see any "datecreate" or any date. I do not see a refference to a cell location for the results in your code, should there be?
    Thanks-Larry

  • Keep trying buddy - but you're not quite following my instructions!




    You need to select "This workbook" which you do by double clicking on the "This workbook" tab of your open workbook in the project explorer pane on the left of the VBE - this is before you select "This Workbook" from the (declarations) drop down box - that's why it isn't in the dropdown box, because you haven't actually selected it from the project explorer pane...

  • I have put a piccy to show you in a word doc.... but i can't upload it from work due to firewall.... :no: so if you or anyone else for that matter want it (or would like to post it for me) give me an email address & I'll send it...

  • Now I found it...when I opened the Microsoft Visual Basic previously, the "Project-VBAProject" payne was closed...so I never saw the "This Workbook" that you were reffering to. By going to "View" at the top I saw the "Project Explorer" and it opened up the Project-VBAProject payne. I don't have time now to try to place the code in the right place (hopefully), but I will tonight. I appreciate you indulgence very much. I also DL Word Doc that was posted. Thanks & I will post back results latter
    Larry

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!