#### if statement with dates

```I would like to hae a statement like:
IF(A1>0,1/1/2004)
interpreting 1/1/2004 as a date.
EXCEL is doing the arithmetic and giving me the result of the division.
I could figure the numeric date and do something like:
IF (A1>0,37987)
but that seems rather awkward.
What else can I do.

TIA
Sanford

```
 0
Lefkowitz (1)
10/22/2004 1:49:08 PM
excel.misc 78881 articles. 5 followers.

1 Replies
472 Views

Similar Articles

[PageSpeed] 36

```Hi
=IF(A1>DATE(2004,1,1),....

--
Regards
Frank Kabel
Frankfurt, Germany

"Sanford Lefkowitz" <Sanford Lefkowitz@discussions.microsoft.com>
schrieb im Newsbeitrag
news:AA55C47A-5BF1-45A5-A6FB-D12E818AE4A9@microsoft.com...
> I would like to hae a statement like:
>  IF(A1>0,1/1/2004)
> interpreting 1/1/2004 as a date.
> EXCEL is doing the arithmetic and giving me the result of the
division.
> I could figure the numeric date and do something like:
>  IF (A1>0,37987)
> but that seems rather awkward.
> What else can I do.
>
> TIA
> Sanford
>
>

```
 0
frank.kabel (11126)
10/22/2004 1:55:03 PM

Similar Artilces:

* In IF Statement
Novice Excel 2003 user who knows absolutely nothing about VBA. I am trying to use an IF statement referencing "1*" in my criteria but it doesn't seem to be able to do this. I have departments "AR" in cell F1, "BSC" in F2, "Finish" in F3, and "Surface" in F4. AR is equal to 1, BSC equal to 2, Finish equal to 3, and Surface equal to 4. In B2:B29 I reference department numbers 1, 2, 3, or 4, and then a letter equal to A through V. Basically what I am looking for is a formula that will enter the department name in C2:C29 based on th...

If Statement inside If statement....
Hello all, I'd like to start by thanking all on this forum for the great advice and assistance you provide! Saved me more times than I can count! OK.... Here is the formula I currently have... =IF(J16="W",ABS(SUM(H16/I16)),- I16) This formula works great however in place of "ABS(SUM(H16/I16))" I need to tell it to do that if the value in H16 is negative. If H16 is positive I want it to perform "=I16*(H16/100)" I'm not sure I explained what I want clearly so I'm going to put it in words.... IF J16 is "W" then if the value in H16 is negat...

How to plot Date Ranges on Same Graph
I'm trying to take our personnel spreadsheet and chart employee arrival and departure data so that we can graphically see where the gaps are and whether or not we need to fill certain positions earlier than expected. When a date changes, the chart needs to automatically re-draw to correct itself. I thought, "oh, this will be easy- I'll simply chart the date ranges with everyone on their own line," and told my boss it would take 5 minutes. Well, that was a week ago! He's getting antsy, and I'm getting embarrassed. So basically, if person X is here from May throu...

date track based on svc cond.
Hello, I'm looking to track material out for repair by the type of service requested. In cell (G:2) will be the type of service: BCK = bench check = this will have a projected return date (I:2)within three days of the repair order date (H:2) REP = repaired = This will have a projected return date (I:2) within 7 days of the repair order date (H:2) OVH = overhaule = this will have a projected return date (I:2) within 14 days of the repair order date. With all this I would also like to include the warranty schedule from the date of the service reurn date(J2) BCK= no warranty REP= 90...

Statements: Numbering and then being able to apply based on state
Have a client who can have 20,000 invoices for a customer in a month. The customer only gets a statement. Would like to be able to number statements and then if the customer pays the full amount be able to pay based on the statement number. Thought about using lockbox processing to try and set up a format based on the electronic data that comes back with the check as well. But need to find if anyone has a solution for the statement numbering We created a customization for a customer about 5-6 years ago that does something like this...it was pretty involved. From what I recall, ther...

Can I create a statement from a spreadsheet
I have created a spreadsheet of 90 people going on a trip for our school showing the amount of the trip and each payment and six or seven payments with check numbers. I also have their current balance. Would I be able to print a statement of their account? The short answer is "yes". However, the best method of doing this, depending on how your data is arranged, is probably to use the XL sheet as a data source for a data merge in Word. In article <2EAF704F-6D0D-4C68-BDF7-EEA5F38EB85E@microsoft.com>, pfb99 <pfb99@discussions.microsoft.com> wrote: > I have crea...

microsoft money 5 date configuration
Microsoft money 5 has always displayed the date as month, day, year. One day it accidently got changed to the show the date as day, month, year. I don't know what happened or how to correct it. Does anyone know How to change it back? In microsoft.public.money, winir wrote: >Microsoft money 5 has always displayed the date as month, day, year. One day >it accidently got changed to the show the date as day, month, year. I don't >know what happened or how to correct it. Does anyone know How to change it >back? Check the short date format in Windows. ...

IF OR Statements
Anybody know how to write a formula for a statement involving "if" and "or" statements such as: if (cell x=? and (cell y=? or cell z=?)) then cell Q=? or blank In general terms: =IF(AND(X=?,OR(Y=?,Z=?)),?,"") This formula would have to be in cell Q An example with actual cells: =IF(AND(A1=10,OR(A2=15,A3=20)),100,"") This formula must be in the cell to which the result should be returned. A formula can't "reach out" and put a value in another cell; it just returns a value to the cell that has the formula. Please note th...

Duration between 2 dates
Hi, I need some help on calculating number of days between two dates excluding weekends and holidays. Is there a function in sql that can do this? Like excel has NETWORKDAYS function. Any help is much appreciated. Thanks! Kru (pkruti@hotmail.com) writes: > I need some help on calculating number of days between two dates > excluding weekends and holidays. Is there a function in sql that can > do this? How would SQL Server know when you have a holiday? The right approach is to have a calendar table. See here for a couple of good articles to study: http://ww...

Due Date in Statement of accounts??
Hi , Can i print the sales statement of account showing the DUE DATE ? If yes, kindly please help me to create the field. Thanks in advance. ...

Date Reminder
How can I get the background of a cell to change color if the date entered is within 60 days of todays date ? Is there a way to make it flash ? Thanks Format/Conditional Formatting change Cell Value is to Formula Is enter =ABS(A1-TODAY())<=60 click Format button, click Patterns tab, pick a color. Change A1 to the cell containing the date. "George" <George@discussions.microsoft.com> wrote in message news:2A714BC5-D1D3-4809-978A-B4C6F712E048@microsoft.com... > How can I get the background of a cell to change color if the date entered > is > within 60 days of to...

Statement Line Items not purging off statement
I have a few customer statements that although payments and returns have been applied to invoices and paid in full are not purging off their statement?? There are a few different ways to achieve this outcome. This method is when NOT using National Accounts. I find most customers do not want to run the Paid Transaction Removal process too soon, in case of voids and credits are required. We modify the RM Statement and add a cacluated field to supress the body when the current transaction amount is zero. First step is to link the RM Open File to the RM_Statements_TRX_Temp, us...

If statement- formula
Well I'm stumped again. If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999 thanx, ~Julz Hi what do you mean with 'enter contents'?. Do you want to add them? If yes try =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199 99,"<>xyz",close!ae2:ae19999) -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Well I'm stumped again. > > If close!ag2:ag19999 contains "xyz" enter contents of > close!ag2:ag19999, otherwise enter cont...

Contract posted with wrong dates
We have had a few issues where a contract is posted and invoice generated. The invoice is generated but not posted. Our contract department would like to be able to cancel the contract or change the start/end dates due to a change in our relationship with the customer. Is there any way to do this or is our only option removing the contract through SQL and creating a new one? ...

Excel Inserting "=" when entering date
can anybody tell me when my excel workbook is inserting a "=" when enter a date. instead of just the date. when i enter a normal numbe the "=" is not inserted i think this is a settings problem but i cant see how to change it Simo -- Message posted from http://www.ExcelForum.com Hi Simon! This is a setting that you can deactivate. In Excel, click on the Tools menu, then click on Options, select the Transition tab and unselect "Transition formula entry". Best regards Stefan H�gglund Microsoft "simeyg >" <<simeyg.1blf1x@excelforum-no...

Details in statement
One of my employees' turned something on in store operations to show details on every statement I print I need to turn it off how? ...

Hi, After I download my statements from Fidelity and another similar institution, I get a page with the results of the download. At the top is a caption which tells me how many unread statements I have. There is a column to the left labeled Unread which may have an arrow pointing to one or more statements. Sometimes, even though the page caption says their are no unread statements, there are arrows in the Unread column to one or more accounts, and occasionally, there are in fact transactions when I review the individual account. Is this contradiction a minor bug in Money 2004, or is it tell...

Dates showing are not what I type
I am sorry if this is a stupid question but here goes . If I type in dates as 010106 it gives me 01/09/27 This was raised in an earlier Question I tried the reply by Ragdyer which was (If you were going to enter a *very* large number of dates into a column, where you might consider it worthwhile using several *extra* steps in Text To Columns (TTC), you could enter your dates using *6* digits (to avoid ambiguity), *without* delimiters. This entails a whole other procedure, so the saving wouldn't be realized unless the number of dates to be entered was quite large. For example, you coul...

Updating Date with Update Query
I have inherited an Access 2003 database that has a table that stores Invoice Number, Invoice Month, Quantities, etc. A user in my team has pointed out that while each record has the Invoice Number filled (because it is part of the primary key), many records are missing the Invoice Month (an optional field.) This user wants the Invoice Month filled. I created an update query to fill in the blanks in the Invoice Month field. In the design of this query, in the "Update to" field I am using an expression like this: IIf([Inv Number]=2,1/1/2010,[Inv Month]) I was planning to...

grouping by dates in pivottable
Hi all, My dataset contains a column of start dates / times formatted as dd/mm/yyyy hh:mm I have created a pivot table with this field as the column headers. I would like to group all the data by Month and year so that my column headers would read: Jun 05 Jul 05 Aug 05 and then the data beneath these headings would be the total occurances in the month. I have done grouping in pivottables before but not with dates and I keep getting an error box saying "Cannot group selection". I am getting :mad: and :confused: trying to find out what the problem is cos I know that thi...

IF statement
Need help with an IF statement. If cell B25 has either 25 or -25, I need cell d7 to return the value of 3 Thank You James, In D7..... =IF(ABS(B25)=25,3,"") John "James Beam" <jimbeam27@earthlink.net> wrote in message news:lShVb.15586\$F23.13831@newsread2.news.pas.earthlink.net... > Need help with an IF statement. > > If cell B25 has either 25 or -25, I need cell d7 to return the value of 3 > > > > Thank You > > Try this in D7: =3*(MOD(B25,25)=0) -- HTH, RD ------------------------------------------------------------------...

Inputting Dates Formula?
Is there anyway to input a date in a cell when data first appears in another cell? For instance, if I entered data in cell A1, is there a way that B1 will record that date, but not change it the next day? Thanks! You may be able to use a macro to enter the time/date so that it doesn't change. Take a look at JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html Scott wrote: > > Is there anyway to input a date in a cell when data first appears in another > cell? For instance, if I entered data in cell A1, is there a way that B1 > will recor...

Conditional Formatting with Dates
I'm having a problem with a simple Conditional Format. I have a column of dates and I want to have the cell highlight in re if the date in the cell is less than today. Example... The date in my cell is 2/1/2003 Today's date is 8/5/2004 I apply a conditional format on the cell like this: Format\Conditional Formating Conditon 1: Formula Is - R2<TODAY() Click OK. Then I Format Pattern and pick the color Red. Note that if I go back and look at it, it now looks like this: Formula Is - ="R2<TODAY()" My cell is not Red, but it should be with the dates above, correct?...

Automatic date/time aquisition for graphs
Ok, I collect particle count data for a cleanroom which looks like th following: "LASAIR110","04/05/19","15:47:41",2700.0,45.00,"V6.3",0,0,0,1,433,486,236,113,294,196,103,328,4.993,1.0000,0.000,0.000,0.000,0.000,0.000,"22de" "LASAIR110","04/05/19","16:33:15",2700.0,45.00,"V6.3",0,0,0,1,458,432,163,55,107,55,46,139,5.007,1.0000,0.000,0.000,0.000,0.000,0.000,2299 "LASAIR110","04/05/19","17:18:49",2700.0,45.00,"V6.3",0,0,0,1,305,286,76,29,14,1,0,0,5.017,1.0000,0.000...

problem with date set up
Hi i download a merchant credit card transaction list. The credit card expirery date is originally Month/year. when it is imported it turns out as day/month/current year. I can not convert this back, if I put in format month year, it just converts the last 2 figures ie month /current year.. Any ideas thanks Tim Hi Try using Data/Text to columns. Select the column and click on Data / Text to columns. On the first two dialog boxes, click next. On the third dialog option box, in the top right corner, tell Excel that the column is a date and the format of that date. Job done! Ma...