Excel Formula

  Abel 21:42 08 Jul 09
Locked

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

Abel

  wiz-king 21:58 08 Jul 09

What version of Excel?

  Picklefactory 22:05 08 Jul 09

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 09

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

Abel

  Abel 22:11 08 Jul 09

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

Abel

  Picklefactory 22:16 08 Jul 09

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 09

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.

Abel

  Picklefactory 22:30 08 Jul 09

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

=IF(G192<>0,"",IF(A192<A1,"Y-","Y"))

  Abel 22:42 08 Jul 09

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.

Abel

  Picklefactory 23:00 08 Jul 09

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

=IF(G192<>0,"",IF(A192<$Z$100,"Y-","Y"))

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 09

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.

Abel

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

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced