Creating a timestamp that is static in a Now formula.

RAMoore2252

New Member
Joined
Dec 10, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
fI am working with a spread sheet to track break times in spreadsheet. When a specific condition is met in a now () statement I want the result to become a static time stamp in another cell inticating the time an individual went on break. What would the formula be to make the time unchangable. I can share the sheet if needed just let me know where to share it. Thank You.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
try
=IF(A2="","",IF(B2="",NOW(),B2))
A2 - enter text manually
B2 - timestamp
Iterative calculation must be enabled
 

RAMoore2252

New Member
Joined
Dec 10, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
try
=IF(A2="","",IF(B2="",NOW(),B2))
A2 - enter text manually
B2 - timestamp
Iterative calculation must be enabled
Hi,
I have three cells that use this formula on the same row. time for break1, time for lunch and time for break 2. The formula works fine for the first break but when the other two breaks are entered it changes the time to be the same in the previous breaks. How do I fix it so that the time does not change. I can send you an actual copy of my work sheet if needed.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
try
Book1 (version 3).xlsb
ABCDEF
2break103:29:51break203:36:35break303:37:30
Sheet4

B2: =IF(A2="","",IF(B2="",NOW(),B2))
D2: =IF(C2="","",IF(D2="",NOW(),D2))
F2: =IF(E2="","",IF(F2="",NOW(),F2))
to use new , eg. break4 you need to delete first text then hit Enter and type new text in the same cell
Book1 (version 3).xlsb
ABCDEF
2break403:42:42break503:43:07break603:43:16
Sheet4
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

hope it will explain better
timestamps.gif
 

RAMoore2252

New Member
Joined
Dec 10, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
hope it will explain better
timestamps.gif
Here is what I am trying to do. When I use the drop down in the Bold Brk 1 I want the time to show as a static value in the Second column as the time. The same is for Lunch indicating a time they went on break in the left column Lunch. and so on for Break 2. The problem is that as the people take their breaks the same time is updated in each of the preceding cells. I have indicated the columns that are used in the formula.
Break 1B1 ReturnLunchL ReturnBreak 2B2 ReturnEndNAMESTATUSBrk 1LunchBrk 2
brk1brk1lunchlunchbrk2brk216:00YONKER, LUKEblankbrk1lunchbrk2
20:59:21:14:17:00CLARK, ALEXblankblankblank
20:59:21:14:17:00DILL, SCOTTblankblankblank
column gcolumn hcolumn Icolumn jcolumn kcolumn lcolumn pcolumn q
column r
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

it doesn't work this way
you need to delete value to see timestamp cell blank then you can enter new value (manually or from DV List)
this is a simple formula using iterative calculation

maybe try any vba, but you will need for someone else who do that for you
 

RAMoore2252

New Member
Joined
Dec 10, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
it doesn't work this way
you need to delete value to see timestamp cell blank then you can enter new value (manually or from DV List)
this is a simple formula using iterative calculation

maybe try any vba, but you will need for someone else who do that for you
Thank you for trying. Seems like excel would simplify the process, since so many people seem to have the same idea as to what I am needing.
 

Forum statistics

Threads
1,136,200
Messages
5,674,379
Members
419,503
Latest member
tismail

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top