Ongoing form w/ TODAY() function


New Member
Nov 3, 2017
I am creating documents that are ongoing data entry throughout the month but I want to be able to insert a TODAY() function based on text input into a certain cell (simply, once someone starts typing, that day's date gets input in the date column). So far so good, my IF function for inserting the date is working lovely. However, I don't know how to convert that date into text automatically as well, without creating a macro or manually copying and "paste value" each time. I'd like to be able to write an IF() function in the box I'd like the date to go in... something along the lines of =IF(C6>" ", TEXT(TODAY(), MM/DD/YYYY), 0) but without using the TODAY() function since a) that doesn't work and b) that defeats the whole purpose.

I thought maybe there might be a function that I could write that would be something similar to =IF(C6>" ", "A5", 0) but it would write what was written IN A5 (the value, not literally "A5" as formulas for Excel dictate). Then I could put this formula where I want the actual date to be, put a form of the TODAY() function within an IF function in a box with the font color white and be on my merry way. But then again, maybe it would still change every time the document was opened or edited....

Short of writing something in VBA or even a simple macro button, is there a function that I can nest in the IF function to make this work? Simply put, I need today's date to be input in a cell but not change tomorrow when I open the form again.

I'm not making these forms for myself, so even though I know how to do this the easy copy/paste way and I realize that a macro would be the easiest thing regardless, I am working on building forms that have functions only. Personally, I go between 2016 and 2007 so I have to exhaust every single option before doing anything macro/VBA so that fixing them is easy for me as well (we all know that even when you protect your doc, someone will find a way to mess it up), which makes it extra difficult.


Forum statistics

Latest member
Shahzad Taimoor

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...