Time Left and If/Then

[Renegade8302]

I have 2 questions.

A B C
1 100 x 100 (If B1 = x, then C1 = 100)
2 100 0 (If B1 = nothing, then C2 = 0)
3

I want an if/then function to check for an "x", if there is an "x" it will return the value.

If it does not have an "x" it returns 0.

Second Question

Let's say that a certain item costs $5. I want to multiply that by the remaining days in the month.

So let's say theres 5 days remaining till month end. Than it would be $25.

I'm so sorry if these are confusing... let me know if you need clarification.

[rylo]

Hi

1) =if(b1="x",a1,"")

2) =(DAY(EOMONTH(NOW(),0))-DAY(NOW()))*5

If you don't have the EOMONTH function, then go tools, addins, and select the analysis toolpak and analysis toolpak - vba options.


rylo

[broro183]

hi,

Answer 1)
=IF(B1="x",A1,0)

Answer 2)
Cell A2 =TODAY()
Cell B2 =DAY(DATE(YEAR($A$2),MONTH($A$2)+1,0))-DAY($A$2)
Cell A4 = $5
Cell B4 = A4*$B$2

You can copy cell B4 down for as many rows as there are items. By putting the "Days left" formula in a separate cell it means that Excel only has to perform the calculation once even when you copy the B4 formula down the page for tens or thousands of items.

The "days left formula" was copied from Chip's below page:
http://www.cpearson.com/Excel/DateTi...tm#DaysInMonth

He also has a lot of useful date & time links at the base of the below page:
http://www.cpearson.com/Excel/datetime.htm


hth
Rob

[broro183]

nice one Rylo,
I thought there was a tidier function but couldn't remember what it was.

Rob