Excel Formula

  Abel 21:42 08 Jul 2009

I want to express a date in an Excel formula such as > 1/1/10. What’s the syntax please?


  wiz-king 21:58 08 Jul 2009

What version of Excel?

  Picklefactory 22:05 08 Jul 2009

I'm not sure you can express a date within a formula in that format, can you give some more detail? What exactly are you trying to do?

  Abel 22:06 08 Jul 2009

wiz-king, it's 2003. Would you know how it's done in Lotus 123 also?


  Abel 22:11 08 Jul 2009

Picklefactory, a formula such as:
@IF(G192<>0,"",@IF(A192 <1/1/10,"Y-","Y")


  Picklefactory 22:16 08 Jul 2009

Is it possible to put your < date (1/1/10 eg) in a cell? I don't think you can use a date in a formula in that format, but if the date is stored in a cell, you can just use the cell ref in the formula.
Is that any use?

  Abel 22:21 08 Jul 2009

Picklefactory, 'fraid not! I want a calulation to do one thing if the date of the entry is prior to 1/1/2010 and another if it's 1/1/2010 or later. The 'A' column is a date column in a cash record.


  Picklefactory 22:30 08 Jul 2009

You can still keep the same formula, but just use a cell ref instead, place 1/1/10 in cell A1 (Or anywhere else you have an unused cell) and then formula is


  Abel 22:42 08 Jul 2009

The lines in the 'A' column are infinitely variable dates of invoices. In January, 2010 VAT reverts to 17½% from 15% so, depending on the date, I want to calculate VAT at its appropriate percentage.
While I appreciate your suggestion, from an accounting point of view it will be messy.


  Picklefactory 23:00 08 Jul 2009

Sorry, I'm either not understanding (Highly likely), or I haven't explained very well (Even liklier). If 1/1/2010 is a fixed date that is to be used in the formula in each row of your sheet, you can place that date anywhere you like on your sheet that is a free cell, it could be in cell Z100. If I'm correct in thinking you have a series of rows with similar formulae copied down the sheet, then simply place your fixed date (1/1/2010) in any convenient unused cell out of the way of anything else you are using. Lets now use cell Z100 to contain 1/1/2010 as a different example, I shouldn't have used A1 as you are using that column for dates already.
My formula should be corrected though, it should be


the $ signs make the fixed date part of the formula an absolute value, so you can click/drag the formula down and all the other values will update to the relevant row, but the fixed date will remain locked at cell Z100.

Am I still barmy, or does that get anywhere near?

  Abel 07:22 09 Jul 2009

Picklefactory, you're far too good natured. Many would have taken my comment of 'messy' as an insult and thought, let them get on with it. I understood and understand your solution completely, and quite happy that it works, it's just that I feel there must be some other way of expressing the date in a formula, without having to use another cell as a reference point. I've been playing around with 'DATEVALUE', but can't quite get the expression right. Also, Microsoft's explanation of its use doesen't help me to know if I'm using it as intended.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?