This is my first time posting, so please bare with me. I am working on a worksheet for hours worked each day of the week. I need the date to be the correct date on start up, but not to change the next consecutive days of the week. I currently have =TODAY() in B2, of course this morning when I brought up the spreadsheet, it had today's date, 3/10/2018. But I need to freeze the date so that it doesn't change tomorrow or the consecutive dates until 7 days later. I hope that my screenshot helps to understand what I need to do.
How to use the =TODAY() for start up but freeze that date
-
-
-
There is no screenshot.
You cannot to this with a formula. The easiest way is to copy and paste values before saving the file.
-
Thank you Ali,
I have attached the spreadsheet that I am working on so maybe it will be easier to understand. We start our work week on Saturdays, so I would need the dates to renew every 7 days. We could always just add the new date on every Saturday, but I was just trying to make it easier for those that are not to familiar with Excel. Thank you for the help -
Alternatively you could use the following keystrokes Ctl + ;
ie. Hold down the control key and press the semi-colon key at the same time to get the current date hard coded.
-
Yes Alan, I have it excel puts it back to =TODAY() and when I opened it up this morning, I had today's date.
-
-
Try this:
=TODAY()-CHOOSE(WEEKDAY(TODAY(),16),0,1,2,3,4,5,6)
-
Thank you Ali. Will this let the template update every Saturday? If not, that is ok. I may just leave and enter the date manually. I was trying to make it easier on others keeping up with this.
-
Well, yes, because that's what you asked for!!!
You can test it yourself. Copy this into a new worksheet in cell A1:
=B1-CHOOSE(WEEKDAY(B1,16),0,1,2,3,4,5,6)
In B1, type today's date (3/12/18) followed by ENTER. What does A1 return?
Now change B1 to 3/13/18 - ENTER. Now what does A1 say?
Now change B1 to 3/17/18 - what does A1 say now?
Once you have satisfied yourself that it works, use this in your timesheet in B2:
=TODAY()-CHOOSE(WEEKDAY(TODAY(),16),0,1,2,3,4,5,6)
-
Thank you Ali for the help
-
You're welcome!
-
-
Respected Ali and Alan,
Can I have the solution like
when an employee enters his/her employee ID , HARD coded date and time is entered in 1 cell , then when its done second time ,it is entered in break start ,then break end and day end when it is done fourth time
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
ROW1 Emp ID DAYstart Breakstart BreakEnd DayEnd calculate HH:MM depending on hours doneZohar Batterywala
-
Zohar - please start your own thread. Thanks.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!