I need for a column to put today's date in automatically, however when i open up the same spreadsheet tomorrow i need for yesterdays date to stay and not change.
OT: Excel question
- Thread starter Col. Forbin
- Start date
What you are trying to accomplish is a little unclear.... it seems you could just type in the date "6/6/2013", and it would remain that no matter when you open the speadsheet. Feel free to clarify exactly what the problem is.
The "=TODAY()" function may come in handy.
The "=TODAY()" function may come in handy.
I am trying to help get a log sheet completed. Boss wanted the date to auto-fill. I am currently using the today() function,but if i were to save and open up tomorrow everything would say 6/7/2013.
I would think there is a conditional function I could use that says "if there is an entry in column b,a=today()". I just dont know how to write it.
I would think there is a conditional function I could use that says "if there is an entry in column b,a=today()". I just dont know how to write it.
Last edited:
if you need to put in today's date and you want it to change automatically use =TODAY()
if you want it show a previous day you can use =TODAY()-1
you can also you =DATE(2013,06,6) to show the date
and =DATE(2013,06,6)-1 and that will give you 6/5/2013
best I can do for you
if you want it show a previous day you can use =TODAY()-1
you can also you =DATE(2013,06,6) to show the date
and =DATE(2013,06,6)-1 and that will give you 6/5/2013
best I can do for you
=if(B1<>"", =today(), "") - what this does is says if cell B1 has text in it, then input today's date in the current cell... otherwise, leave the current cell blank.... This will still put the current date in the current cell every time you open the workbook, however (I think).
Not to sound like an ***, but if your boss wants to enter a date that isn't going to change the next day, I'm still not grasping why he/she wouldn't just enter the actual date. There are definitely some If functions that could be written to tell Excel when and when not to use the today() function, but I think it would honestly be easier just to enter the actual date.
How many days will you be tracking? If a known end date then enter it in a specific cell a use a formula to count backwards IE cell A1-1, -2 etc to get back to current date
You'll have to write a macro to do that. It'll start with:
Private Sub Workbook_Open()
Google this for an example:
excel vba macro automatically fill in today's date</pre>
Private Sub Workbook_Open()
Google this for an example:
excel vba macro automatically fill in today's date</pre>
its a vba thing. pm me your email and I will send you a spreadsheet that will do what you want. After a year-long hiatus, I joined to respond to you so count yourself lucky.
This is the code, but I can send you a working spreadsheet that does it automatically on open:
Option Explicit
Sub today()
Application.ScreenUpdating = False ' turn off screen updating
On Error GoTo errorhandler ' if an error goto errorhandler
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then ' make sure some cells have an entry
'Search for the entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Else
Cells(1, 1) = Date ' if no cells have an entry then cell 1,1 (A1) gets todays date
End If
If Cells(LastRow, 1) = Date Then 'if the the last row in column 1 (A(lastrow)) is todays date then do nothing
Else
Cells(LastRow + 1, 1) = Date ' if the last row in column 1 (A(lastrow)) is not
'todays date then make the next row todays date
End If
Application.ScreenUpdating = True 'turn on screen updating
errorhandler:
Application.ScreenUpdating = True ' turn on screen updating even if the code bugs out due to error
End Sub
This is the code, but I can send you a working spreadsheet that does it automatically on open:
Option Explicit
Sub today()
Application.ScreenUpdating = False ' turn off screen updating
On Error GoTo errorhandler ' if an error goto errorhandler
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then ' make sure some cells have an entry
'Search for the entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Else
Cells(1, 1) = Date ' if no cells have an entry then cell 1,1 (A1) gets todays date
End If
If Cells(LastRow, 1) = Date Then 'if the the last row in column 1 (A(lastrow)) is todays date then do nothing
Else
Cells(LastRow + 1, 1) = Date ' if the last row in column 1 (A(lastrow)) is not
'todays date then make the next row todays date
End If
Application.ScreenUpdating = True 'turn on screen updating
errorhandler:
Application.ScreenUpdating = True ' turn on screen updating even if the code bugs out due to error
End Sub
Last edited: