Excel formula help?

Question

I have the below formula to calculate the number of days between a random date and the last working day. And I am not sure what to enter in place of the question mark so that it always uses the last working date? B9 is where my random date is located.

=DAYS360(B9,?,TRUE)

Thanks!

And how do I enter this as an IF function? That is it should only calulate the number of days if B9>1.

Answer 1

You will have to reference another cell where you have the following formula, "=TODAY()" Do not enter anything in the brackets just enter it as I wrote it and then reference that cell in your current formula. Don't enter in the "" though.

It will always reference the current date. The only other option is to manually enter a date in a cell and reference that cell like this one.

Ok... Here is your additional formula. =if(B9>1,(DAYS360

(B9,Z1,TRUE)),"") for some reason, when I try and enter the formula here it cuts the last portion and puts in ... so I had to split the formula. There should be no space between 360 and (B9.

Z1 is where I entered the =today() formula and "" will leave the cell blank. You can put whatever you want in the last quotes and it will pop that in the field if B9 is greater than 1. Note, that having 1 in that field will not cause the calculation, only havng 2 or greater will cause the calc. If you want 1 to trigger it also, use >0.

To manually enter todays date in a cell, you can also use "ctrl :"

Answer 2

The following formulas assume that when you stated "working day", you meant Monday thru Friday.

Use this if you DO NOT count the current day (if it is a working day):

=IF(B9>1,DAYS360(B9,IF(WEEKDAY (TODAY()-1,2)>5,IF(WEEKDAY(TOD AY()-1,2)=6,TODAY()-2,TODAY()- 3),TODAY()-1),TRUE),"")

Use this if you DO count the current day (if it is a working day):

=IF(B9>1,DAYS360(B9,IF(WEEKDAY (TODAY(),2)>5,IF(WEEKDAY(TODAY (),2)=6,TODAY()-1,TODAY()-2),T ODAY()),TRUE),"")

Answer 3

=DAYS360(B9;NOW();TRUE)

instead of B9
Use $B$9 for fix its place

=NOW()
gives the current date
you can write it at $A$1

Then
=DAYS360($B$9;$A$1;TRUE)