Using an IF statement to insert the time using NOW() but then not update it

TescoCFCLoader

New Member
Joined
Oct 20, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I'm working on a spreadsheet to track how many delivery vans I've loaded each day.

The loading sheets have barcodes at the bottom which I scan into my table. They're in the format T<trip><date_as_yyyymmdd>V<van_number>, i.e. T001A20221024V101.

At work the people on the desk have a spreadsheet (on SharePoint) which the manager monitors for loader performance. When our card is scanned to enter our name next to each trip the Start Time (NOW()) is entered by using a circular reference to prevent it from updating.

I'm trying to incorporate that into my own spreadsheet but I can't figure out how to do it - it's either returning the word FALSE or it's inputting the time but updating it. I don't get chance to look at the formulae as it's in constant use from 3:30am until approximately 7:30pm every day (and us loaders aren't allowed behind the desk).

The formula I have (in C2) currently is:
=IF(ISBLANK(C2),IF(ISBLANK(A2),"",NOW()))
and this is returning FALSE in all cells in the row (including those where column A (Scan) is blank).

If I change it to:
=IF(ISBLANK(A2),"",IF(ISBLANK(C2),NOW()))
it still returns FALSE but only in the rows where Scan is not blank.

If I change it to:
=IF(ISBLANK(A2),"",IF(ISBLANK(C2),"",NOW()))
it returns the time but updates on each change I make.

I need it to only enter the time in C2 when A2 is not blank and C2 was previously blank.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try
Excel Formula:
=IF(A2="","",IF(C2="",NOW(),C2))
 
Upvote 0
Solution

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top