application development excel - formulas

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » application development excel - Creating formulas

application development excel - Creating formulas

resolvedResolved · Low Priority · Version Standard

Lisa has attended:
Excel Intermediate course

Creating formulas

I would like create a formula which will look at two columns and the current date and if the second column is greater than the first by less than 14 day it should do nothing but if it is greater then the first by more that 14 days to enter overdue in the next column.............can you advise me how to do this?
I would also like to know if it is possible to use symbols such as a tick within formulas?

RE: Formulas

Hi Lisa,

I would use the IF function, it compares two values in your case two dates and can give text responses.

=IF(B1-A1>14, "Overdue", " ") would be one way to do this. Where A1 and B1 have dates in them. It translates to IF the second cell minus the first cell is bigger than 14 then to show the word "Overdue" and it this is not the case to show nothing. This formula can be copied down the column.

Formulas deal with cell references, values and operators such as + - * / a tick being a symbol is not used. What type of thing did you want to do?

Tracy

RE: Formulas

Thank you for youo response
Wantd to put a tick or something else to say something had been done but i guess i would have yo use a word :-)

RE: Formulas

Hi Lisa

Thanks for your question.

This could be handled by using the IF function to determine if the difference between two different dates then enter "overdue" if there is more than 14 days difference between the two dates.

If the first date is in column A and the second date is in column B, then the formula would look like this:
=IF((B?-A?)>14,"overdue","")

where ? represents the row number.

You refer to the current date in your question as well - I'm not sure how this fits in with what you want to be able to do, but if you enter =today() into a cell this will give you the current date, then you could use the cell that has the current date in it as part of the formula provided above.

To my knowledge, trying to use a symbol in a formula does not work particularly well - I don't think you can insert a symbol directly into a formula and if you try inserting a symbol then copying and pasting it into a formula this doesn't seem to work either.

I hope this helps to answer your question.
Amanda


 

Excel tip:

Selecting constant values only

If periodically you need to change all your values back to zero, but leave formulas, text and blank cells as they are select the entire worksheet, choose F5 function key, Special and then Constants and choose the appropriate sub-selections. To enter zero in all the selected cells type 0 and then press Ctrl+Enter.

View all Excel hints and tips


Server loaded in 0.12 secs.