Now() Function not saving date correctly

[arooney88]

Hey everyone! Thanks for any input on this issue I'm having.

I have a workbook that has assignments that get done throughout the month. I need the date to autopopulate based on criteria so I am using the IF() and NOW() functions. The date populates fine, except if I save and close and then come back to it the next day, the date that should be yesterday's date would now show todays date instead. I need it to keep the initial date because I need to do a daily tracker.

Below is the formula I'm using. Once I enter an answer into A2, it should check if A1 has info, then in B1 the date that I entered the answer should populte. This works, but it just won't save the date day to day. It will refresh to current day when you reopen the file. (A2 and B2 should be a merged cell so there won't be anything in that field)



[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]PROCID123
[/TD]
[TD]=IF(A2<>"", IF(A1<>0, NOW(), ""), "")
[/TD]
[/TR]
[TR]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[arooney88]

I do have to enable macros when I reopen the worksheet. Would this be affecting the date?

 

[JLGWhiz]

I do have to enable macros when I reopen the worksheet. Would this be affecting the date?

The formula you are using automatically enters the current date if cell A1 contains data, so the only way to keep the same date is to eliminate the formula and just type in the date you want to show for that cell.

 

[arooney88]

This workbook is really a QA sheet. Cell A1 has a formula that pulls from an assignment sheet, where that cell only gets filled when data is input on the assignment sheet. There are 340 assignments each month and once they get assigned, the Proc ID info fills for all 340 across at the same time. (So using the above example, A1, C1, E1, G1, etc would get filled)

I don't want todays date to populate when all 340 get assigned at once, I only want to record the date when I'm actually working on that assignment. So that's why I have the NOW() function checking if the Proc ID cells are filled AND I have started my responses in A2, A3, A4, etc.

 

[arooney88]

The formula you are using automatically enters the current date if cell A1 contains data, so the only way to keep the same date is to eliminate the formula and just type in the date you want to show for that cell.

It should only populate the date if I have info in cell A1 and A2. I don't want it to populate if there is only info in A1.

 

[JLGWhiz]

How about:

=If(AND(A1<>"", A2<>""), NOW(), "")

 

[arooney88]

This is similiar what I got to work.

I tried =IF(A1<>"", A2<>""), IF(A1="", "", IF(B1="", NOW(), B1)), "")

This seems to be working. Unfortuntely I'm on a work computer so I can't change my time/date to test it out until the morning.