Excel static time function

1. Tools >> >> >> >> >> >> >"Iterative calculations"checkbox.

2. B1 input formula; =if(a1="","",if(b1="",text(now(),"yyyy/m/d hh:mm"),b1))

3. Pay attention to enter one or two spaces between the letters d and h to distance the date and time.

4. If you need more seconds, change the hh:mm to hh:mm:ss, and it will show a few more hours, minutes, and seconds. h stands for hours; m stands for how many minutes; s stands for seconds.

Tools > Options > Recalculate >> Check: Iterative Calculation.

b1 Enter the formula.

if(a1<>"",b1,now())

Fill down. After A1 is entered, the B1 value is fixed.

VBA method: right-click on the worksheet tab >> >> >> >> >> >> >> >> &

private sub worksheet_change(byval target as range)

for each c in

with c

if .column <>1 then exit subif len(.value) >0 and len(.

offset(0, 1)) = 0 then .offset(0, 1) = now

end with

nextend sub

BTW: Personally, I think that to deal with this problem, key combinations are the most concise way, followed by VBA, and the formula is the last.

Your problem is not easy to solve according to your thinking. The only way to do this is to quickly enter a current date that will never change, when you need it.

Select the corresponding cell and press "Ctrl+; key combination to enter the system date.

Array formula, end with shift+ctrl+enter 3 keys pressed together to show the effect, otherwise only one of the cells can be returned.

The function of excel to calculate the static investment period is as follows:

lookup(0, cumulative cash flow pre-tax, year) + abs(lookup(lookup(0, cumulative cash flow pre-tax, year), year, cumulative cash flow pre-tax)) lookup(lookup(0, cumulative cash flow pre-tax, year) + 1, year, cash flow pre-tax).

lookup(0, cumulative cash flow after tax, year) + abs(lookup(lookup(0, cumulative cash flow after tax, year), year, cumulative cash flow after tax)) lookup(lookup(0, cumulative cash flow after tax, year) + 1, year, cash flow after tax).

The static investment period (referred to as the period) refers to the total time required to offset the original total investment with the net operating cash flow of the investment project. It has"Including the investment period (PP) during the construction period"with"Excludes the investment period (PP) during the construction period"Two forms. Its units are usually used"years"Denote.

The investment period is generally calculated from the year of the start of construction, or from the year of investment, and should be specifically indicated when calculating.

The static investment period is the time required to recover the initial investment when the time value of the funds is not considered, and the following formula should be satisfied

T term of the sum of the previous t terms (CI-CO) = 0

where: CI - cash inflow, CO - cash outflow; - Net income in year t, t=1,2,·· pt; PT - Static Investment** Period.

The calculated static investment period should be compared with the benchmark investment period of the industry or sector, and if it is less than or equal to the benchmark investment period of the industry or sector, the project is considered acceptable, otherwise it is not feasible.

Because there are too many parameters, there is no direct function for this, but you can set the formula for calculating it yourself: static investment ** period = (the year when the cumulative net cash flow began to show a positive value) - 1 + (the absolute value of the cumulative net cash flow of the previous year and the net cash flow of the current year).

There is no direct function for this, but you can set the formula for calculating it yourself: static investment ** period = (the year in which the cumulative net cash flow began to show a positive value) - 1 + (the absolute value of the cumulative net cash flow of the previous year and the net cash flow of the current year).

The time of =today() is updated with the system time.

Here's a reference for you: the time of entry.

Menu bar, Tools, Options, Recalculate, tick iterative calculation, set the maximum number of iterations to 1 time in B1 to enter the formula.

if(a1="","",if(b1="",text(if(b1>0,now(),""),"yyyy-m-d h:m"),b1))

This way, whenever you type something in A1, B1 will always freeze the time when you typed in A1, and it won't change whether you turn it off and on again, or do something else.

Remember that A1 does not enter anything first, and then enter the formula after it is written.

If you want to display the current time, this function is the right one. Today is January 1st, and if you open it tomorrow, it will naturally become January 2nd, isn't that the correct result you want to display the current time?

I don't really understand what kind of display results you need.

Copy the cells with formulas and paste the values.

The now function can be used to obtain the current system time.

But to achieve your purpose, you need to use VBA or circular references.

You can use the now() function.

Excel version reference: 2010

1. B1 input formula: =text(if(a1=.)"","",now())"e-m-d hh:mm:ss")

2. Enter, A1 input data, test the effect.

Insert a static date and time.

Current Date Select a cell and press CTRL+;

Current Time Select a cell and press Ctrl+Shift+;

Current Date & Time Select a cell and press CTRL+; , then press the spacebar, and finally Ctrl+Shift+;.

If it is a continuous cell, you can fill it with a stay; For discrete cells, you can use the positioning filling method. Fill the cells you want to enter on the current date at a time.

ps: I don't know your specific needs, I think it is better to use the positioning filling method. If you are convenient, you can leave your contact information on the private message.

Ctrl + try.

Select Column A.

ctrl +

Press Enter Ctrl + D

Be happy, and if you are happy, take it;

Use the today() function to return the current date.

Then by judgment.

today()-a1 to update the time.

It's still difficult to update functions automatically.

Time doesn't change. This time is just a display method, there is no addition or subtraction. It may be added or subtracted as a 20150701 number, and the date is just a display pattern. -2 is all 20150699.

For example, a1 is 2015 07 01

B1 input. int((a1-today())/365)&"Year after day","whole")

There is a function formula days360, but unfortunately it is calculated according to the 360 days of a year.