CURRENT DATE KEEPS REVERTING BACK TO 13/01/2000

[Ding080]

H again, 

For some reason the dates in the rest of the column keep reverting back to an older date when data is entered. This is my formula - =IF(Q17<>"",IF(S17="",NOW(),Q17),"")

[#2]

@Ding080 I suspect that in such cases the formula is returning the value from column Q17 and not the value for NOW().

If the value in Q17, for example, equals 13.333333 you formula will return the 13th of January 1900, 8:00 as 13.33333 is the date/time representation for the 13th day as from January 1, 1900 (the start of the Excel calendar). The time comes from the decimals meaning that we are one-third into the day, i.e. 8AM (0.0333333 X 24)

[#3]

oh ok, is there a way to prevent this from happening? If i enter a new row of data and the date cell gets filled automatically it changes all the surrounding dates back to 13/01/00

[#4]

@Ding080 Sorry, don't follow ".....it changes all the surrounding dates back to 13/01/00"

Can you share a file (via Onedrive or similar) or at least provide a screenshot that shows a more complete picture? I.e. not just a few cells, one of which has 13/01/00. What's in columns Q and S, for instance?

[#5]

@Riny_van_Eekelen 

What is meant to happen is once a selection is made in the drop down menu of column P, column Q is filled (multiplication formula of other cells) and then the date in cell S is filled with the current date.

This happens as it should until data is entered into another row, once i select an option in column P (with the drop down menu), column Q is filled, as is Column S, but any other existing dates in column S are changed to 13/01/00

[#6]

@Ding080 Better, but I don't see the row numbers. Is the formula shown by any chance in cell S12? If so, it's referencing itself and gives rise to circular references which could explain erratic behaviour. 

[#7]

yes, thats right S12.