Adding Date Formula

Hello, I have these two monster formulas that I want to add together to get 
a grand total of time.

One is:

=YEAR(C9)-YEAR(C8)-IF(OR(MONTH(C9)<MONTH(C8),AND(MONTH(C9)=MONTH(C8), 
DAY(C9)<DAY(C8))),1,0)&" years, "&MONTH(C9)-MONTH(C8)+IF(AND(MONTH(C9) 
<=MONTH(C8),DAY(C9)<DAY(C8)),11,IF(AND(MONTH(C9)<MONTH(C8),DAY(C9) 
 >=DAY(C8)),12,IF(AND(MONTH(C9)>MONTH(C8),DAY(C9)<DAY(C8)),-1)))&" months, 
"&C9-DATE(YEAR(C9),MONTH(C9)-IF(DAY(C9)<DAY(C8),1,0),DAY(C8))&" days"

I want to add the above with:

=YEAR(C12)-YEAR(C11)-IF(OR(MONTH(C12)<MONTH(C11),AND(MONTH(C12)=MONTH(C11), 
DAY(C12)<DAY(C11))),1,0)&" years, "&MONTH(C12)-MONTH(C11)+IF(AND(MONTH(C12) 
<=MONTH(C11),DAY(C12)<DAY(C11)),11,IF(AND(MONTH(C12)<MONTH(C11),DAY(C12) 
 >=DAY(C11)),12,IF(AND(MONTH(C12)>MONTH(C11),DAY(C12)<DAY(C11)),-1)))&" 
months, 
"&C12-DATE(YEAR(C12),MONTH(C12)-IF(DAY(C12)<DAY(C11),1,0),DAY(C11))&" days"

to get a total of the two.

I have a date in cell C8 and in C9 and then next dates entered are in cell 
C11 and C12.

Thanks!


0
10/16/2007 6:07:32 PM
excel 39879 articles. 2 followers. Follow

7 Replies
410 Views

Similar Articles

[PageSpeed] 39

On Tue, 16 Oct 2007 13:07:32 -0500, "Catfish" <catfishtheman@hotmail.com>
wrote:

>Hello, I have these two monster formulas that I want to add together to get 
>a grand total of time.
>
>One is:
>
>=YEAR(C9)-YEAR(C8)-IF(OR(MONTH(C9)<MONTH(C8),AND(MONTH(C9)=MONTH(C8), 
>DAY(C9)<DAY(C8))),1,0)&" years, "&MONTH(C9)-MONTH(C8)+IF(AND(MONTH(C9) 
><=MONTH(C8),DAY(C9)<DAY(C8)),11,IF(AND(MONTH(C9)<MONTH(C8),DAY(C9) 
> >=DAY(C8)),12,IF(AND(MONTH(C9)>MONTH(C8),DAY(C9)<DAY(C8)),-1)))&" months, 
>"&C9-DATE(YEAR(C9),MONTH(C9)-IF(DAY(C9)<DAY(C8),1,0),DAY(C8))&" days"
>
>I want to add the above with:
>
>=YEAR(C12)-YEAR(C11)-IF(OR(MONTH(C12)<MONTH(C11),AND(MONTH(C12)=MONTH(C11), 
>DAY(C12)<DAY(C11))),1,0)&" years, "&MONTH(C12)-MONTH(C11)+IF(AND(MONTH(C12) 
><=MONTH(C11),DAY(C12)<DAY(C11)),11,IF(AND(MONTH(C12)<MONTH(C11),DAY(C12) 
> >=DAY(C11)),12,IF(AND(MONTH(C12)>MONTH(C11),DAY(C12)<DAY(C11)),-1)))&" 
>months, 
>"&C12-DATE(YEAR(C12),MONTH(C12)-IF(DAY(C12)<DAY(C11),1,0),DAY(C11))&" days"
>
>to get a total of the two.
>
>I have a date in cell C8 and in C9 and then next dates entered are in cell 
>C11 and C12.
>
>Thanks!
>

Perhaps if you could describe in words exactly what you are trying to do with
the dates in C8 C9 C11 C12 someone might be able to come up with a simpler
formula.

For example, it appears as if your first formula can be duplicated by using:

=DATEDIF(C8,C9,"y")&" years "&
DATEDIF(C8,C9,"m")&" months "&
DATEDIF(C8,C9,"md")&" days"

It's shorter and possible easier to understand.  It also has the same errors as
your formula:

C8:	31 Jan 2007
C9:	 1 Mar 2007

Your formula:

"0 years, 1 months, -2 days"

DATEDIF formula:

0 years 1 months -2 days

Both also do not change plural/singular for the years/months/days.

To add the two time intervals, though, you'd need to make some decisions about
lengths of a month and year.

What if, for example, you came out with the following:

First pair:
	1 years, 11 months,  16 days

Second pair:
	0 years,  5 months,   15 days

Since a month can have 28-31 days, there are several possible solutions.

So it really depends on what you want to do.

The simplest method might be to just add up the number of days, and then define
a year as 365.25 days, a month as 365.25/12 days, and use the remainder left
over as days.

But there are other possible methods.




--ron
0
ronrosenfeld (3122)
10/16/2007 6:46:51 PM
I want to calculate the following:
cell C8 = 08/03/1981 starting date
cell C9 = 09/01/2002 ending date

add the above date result with

cell C11 = 05/15/2006 restarting date
cell C12 = Today's date.

With my formula the top result was 21 years, 0 months, 30 days
next was 1 years, 5 months, 0 days

I'd like to add/combine these two somehow.  Maybe just one formula minus the 
missing days between.
The result with years, months, and days is nice.

The huge formula was one found online and not mine.  A simplar one would be 
nice!
I'm looking at your reply below for additional info.

Thank you so much for replying!

Mike


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:3e0ah3doqkcrqmgmbekg7hp37509occmqr@4ax.com...
> On Tue, 16 Oct 2007 13:07:32 -0500, "Catfish" <catfishtheman@hotmail.com>
> wrote:
>
>>Hello, I have these two monster formulas that I want to add together to 
>>get
>>a grand total of time.
>>
>>One is:
>>
>>=YEAR(C9)-YEAR(C8)-IF(OR(MONTH(C9)<MONTH(C8),AND(MONTH(C9)=MONTH(C8),
>>DAY(C9)<DAY(C8))),1,0)&" years, "&MONTH(C9)-MONTH(C8)+IF(AND(MONTH(C9)
>><=MONTH(C8),DAY(C9)<DAY(C8)),11,IF(AND(MONTH(C9)<MONTH(C8),DAY(C9)
>> >=DAY(C8)),12,IF(AND(MONTH(C9)>MONTH(C8),DAY(C9)<DAY(C8)),-1)))&" months,
>>"&C9-DATE(YEAR(C9),MONTH(C9)-IF(DAY(C9)<DAY(C8),1,0),DAY(C8))&" days"
>>
>>I want to add the above with:
>>
>>=YEAR(C12)-YEAR(C11)-IF(OR(MONTH(C12)<MONTH(C11),AND(MONTH(C12)=MONTH(C11),
>>DAY(C12)<DAY(C11))),1,0)&" years, 
>>"&MONTH(C12)-MONTH(C11)+IF(AND(MONTH(C12)
>><=MONTH(C11),DAY(C12)<DAY(C11)),11,IF(AND(MONTH(C12)<MONTH(C11),DAY(C12)
>> >=DAY(C11)),12,IF(AND(MONTH(C12)>MONTH(C11),DAY(C12)<DAY(C11)),-1)))&"
>>months,
>>"&C12-DATE(YEAR(C12),MONTH(C12)-IF(DAY(C12)<DAY(C11),1,0),DAY(C11))&" 
>>days"
>>
>>to get a total of the two.
>>
>>I have a date in cell C8 and in C9 and then next dates entered are in cell
>>C11 and C12.
>>
>>Thanks!
>>
>
> Perhaps if you could describe in words exactly what you are trying to do 
> with
> the dates in C8 C9 C11 C12 someone might be able to come up with a simpler
> formula.
>
> For example, it appears as if your first formula can be duplicated by 
> using:
>
> =DATEDIF(C8,C9,"y")&" years "&
> DATEDIF(C8,C9,"m")&" months "&
> DATEDIF(C8,C9,"md")&" days"
>
> It's shorter and possible easier to understand.  It also has the same 
> errors as
> your formula:
>
> C8: 31 Jan 2007
> C9: 1 Mar 2007
>
> Your formula:
>
> "0 years, 1 months, -2 days"
>
> DATEDIF formula:
>
> 0 years 1 months -2 days
>
> Both also do not change plural/singular for the years/months/days.
>
> To add the two time intervals, though, you'd need to make some decisions 
> about
> lengths of a month and year.
>
> What if, for example, you came out with the following:
>
> First pair:
> 1 years, 11 months,  16 days
>
> Second pair:
> 0 years,  5 months,   15 days
>
> Since a month can have 28-31 days, there are several possible solutions.
>
> So it really depends on what you want to do.
>
> The simplest method might be to just add up the number of days, and then 
> define
> a year as 365.25 days, a month as 365.25/12 days, and use the remainder 
> left
> over as days.
>
> But there are other possible methods.
>
>
>
>
> --ron 


0
10/16/2007 7:09:11 PM
On Tue, 16 Oct 2007 14:09:11 -0500, "Catfish" <catfishtheman@hotmail.com>
wrote:

>I want to calculate the following:
>cell C8 = 08/03/1981 starting date
>cell C9 = 09/01/2002 ending date
>
>add the above date result with
>
>cell C11 = 05/15/2006 restarting date
>cell C12 = Today's date.
>
>With my formula the top result was 21 years, 0 months, 30 days
>next was 1 years, 5 months, 0 days
>
>I'd like to add/combine these two somehow.  Maybe just one formula minus the 
>missing days between.
>The result with years, months, and days is nice.
>
>The huge formula was one found online and not mine.  A simplar one would be 
>nice!
>I'm looking at your reply below for additional info.
>
>Thank you so much for replying!
>
>Mike

Several comments.

First of all, the DATEDIFF formula I gave was slightly wrong.  It should be:

=DATEDIF(C8,C9,"y")&" years "&
DATEDIF(C8,C9,"ym")&" months "&
DATEDIF(C8,C9,"md")&" days"

(The error was in line 2 where "m" should have been "ym" as above).

Second:  I get different results than what you wrote for the dates using your
formula as posted:

21 years 0 months 29 days
1 years 5 months 1 days


Either you did not post the results you have, or perhaps you are also using
times in C8-C11 and that is throwing things off.

Third, and most important, you still need to decide what you want to do about
defining "month" and "year".  See my previous post.
--ron
0
ronrosenfeld (3122)
10/16/2007 7:30:58 PM
Well what about using your datedif forumla example only taking my original 
C12 Today's date - C8 date
then subtract 1351 days inbetween the C9 and C11 date?  This may not be 
possible.

I understand what
you were talking about on the defining months and days since there are 
different number of days in months.

Your formula is so much simpler than my original one.

Thanks again!

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:r54ah3drilnrqnkbu5sirl1et9tgec2a19@4ax.com...
> On Tue, 16 Oct 2007 14:09:11 -0500, "Catfish" <catfishtheman@hotmail.com>
> wrote:
>
>>I want to calculate the following:
>>cell C8 = 08/03/1981 starting date
>>cell C9 = 09/01/2002 ending date
>>
>>add the above date result with
>>
>>cell C11 = 05/15/2006 restarting date
>>cell C12 = Today's date.
>>
>>With my formula the top result was 21 years, 0 months, 30 days
>>next was 1 years, 5 months, 0 days
>>
>>I'd like to add/combine these two somehow.  Maybe just one formula minus 
>>the
>>missing days between.
>>The result with years, months, and days is nice.
>>
>>The huge formula was one found online and not mine.  A simplar one would 
>>be
>>nice!
>>I'm looking at your reply below for additional info.
>>
>>Thank you so much for replying!
>>
>>Mike
>
> Several comments.
>
> First of all, the DATEDIFF formula I gave was slightly wrong.  It should 
> be:
>
> =DATEDIF(C8,C9,"y")&" years "&
> DATEDIF(C8,C9,"ym")&" months "&
> DATEDIF(C8,C9,"md")&" days"
>
> (The error was in line 2 where "m" should have been "ym" as above).
>
> Second:  I get different results than what you wrote for the dates using 
> your
> formula as posted:
>
> 21 years 0 months 29 days
> 1 years 5 months 1 days
>
>
> Either you did not post the results you have, or perhaps you are also 
> using
> times in C8-C11 and that is throwing things off.
>
> Third, and most important, you still need to decide what you want to do 
> about
> defining "month" and "year".  See my previous post.
> --ron 


0
10/16/2007 8:10:27 PM
On Tue, 16 Oct 2007 15:10:27 -0500, "Catfish" <catfishtheman@hotmail.com>
wrote:

>Well what about using your datedif forumla example only taking my original 
>C12 Today's date - C8 date
>then subtract 1351 days inbetween the C9 and C11 date?  This may not be 
>possible.
>
>I understand what
>you were talking about on the defining months and days since there are 
>different number of days in months.
>
>Your formula is so much simpler than my original one.
>
>Thanks again!

If you are just going to look at the numbers of elapsed days, it would be
simply:

=C9-C8 + C12 - C11

Format the result as "General"

For your values, I get 8,218 days.

You could then use the following formulas:

A1:	=C9-C8+C12-C11

A3:	=INT(A1/365.25)
  Format/Cells/Number/Custom Type: 0" years"

A4:	=INT((A1-A3*365.25)/(365.25/12))
  Format/Cells/Number/Custom Type: 0" months"

A5:	=INT(A1-A3*365.25-A4*365.25/12)
  Format/Cells/Number/Custom Type: 0" days"

Or something similar, depending on how you wanted to define months and years.

You could also put it all into a single cell and output a text string, but the
formula is "messy":

=TEXT(INT((C9-C8+C12-C11)/365.25),"0"" yrs""")&
TEXT(INT((C9-C8+C12-C11-INT((C9-C8+C12-C11)
/365.25)*365.25)/(365.25/12)),"\, 0 ""months""") &
TEXT(INT(C9-C8+C12-C11-INT((C9-C8+C12-C11)/
365.25)*365.25-INT((C9-C8+C12-C11-A3*365.25)/
(365.25/12))*365.25/12),"\, 0 ""days""")



--ron
0
ronrosenfeld (3122)
10/16/2007 9:45:38 PM
You Rock!

Worked great.

Thank you sir!  I owe ya the biggest hamburger in Texas!



"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:l3bah3ll2o76bm3dm75qic4l0cq5g5nhla@4ax.com...
> On Tue, 16 Oct 2007 15:10:27 -0500, "Catfish" <catfishtheman@hotmail.com>
> wrote:
>
>>Well what about using your datedif forumla example only taking my original
>>C12 Today's date - C8 date
>>then subtract 1351 days inbetween the C9 and C11 date?  This may not be
>>possible.
>>
>>I understand what
>>you were talking about on the defining months and days since there are
>>different number of days in months.
>>
>>Your formula is so much simpler than my original one.
>>
>>Thanks again!
>
> If you are just going to look at the numbers of elapsed days, it would be
> simply:
>
> =C9-C8 + C12 - C11
>
> Format the result as "General"
>
> For your values, I get 8,218 days.
>
> You could then use the following formulas:
>
> A1: =C9-C8+C12-C11
>
> A3: =INT(A1/365.25)
>  Format/Cells/Number/Custom Type: 0" years"
>
> A4: =INT((A1-A3*365.25)/(365.25/12))
>  Format/Cells/Number/Custom Type: 0" months"
>
> A5: =INT(A1-A3*365.25-A4*365.25/12)
>  Format/Cells/Number/Custom Type: 0" days"
>
> Or something similar, depending on how you wanted to define months and 
> years.
>
> You could also put it all into a single cell and output a text string, but 
> the
> formula is "messy":
>
> =TEXT(INT((C9-C8+C12-C11)/365.25),"0"" yrs""")&
> TEXT(INT((C9-C8+C12-C11-INT((C9-C8+C12-C11)
> /365.25)*365.25)/(365.25/12)),"\, 0 ""months""") &
> TEXT(INT(C9-C8+C12-C11-INT((C9-C8+C12-C11)/
> 365.25)*365.25-INT((C9-C8+C12-C11-A3*365.25)/
> (365.25/12))*365.25/12),"\, 0 ""days""")
>
>
>
> --ron 


0
10/16/2007 11:23:03 PM
On Tue, 16 Oct 2007 18:23:03 -0500, "Catfish" <catfishtheman@hotmail.com>
wrote:

>You Rock!
>
>Worked great.
>
>Thank you sir!  I owe ya the biggest hamburger in Texas!

You're on!  (The next time I'm in Texas :-))


--ron
0
ronrosenfeld (3122)
10/17/2007 12:37:52 AM
Reply:

Similar Artilces:

date calcuation
how would one express 1st Monday 3 months later? given start date in a1 as 2007-06-11 adding 3 months is easy: =date(year(a10),month(a1)+3, day) but how do I make sure if that is not Monday, I grab the coming Monday? hopefully I don't macro for that Here you go, with start date in A10 (you used both A10 and A1) =DATE(YEAR(A10),MONTH(A10)+3, DAY(A10))-WEEKDAY(DATE(YEAR(A10),MONTH(A10)+3, DAY(A10))-2)+7 -- Regards, Peo Sjoblom "gs" <gs@dontMail.telus> wrote in message news:uKqAp$QrHHA.4764@TK2MSFTNGP06.phx.gbl... > how would one express 1st Monday 3 mont...

Cell refering filename in formula bar
How do you refer filename1 to a cell in the formula bar? Example: ='\\Year 2004\September\[_filename1_.xls]sheet1'!$A$1 A | B | C 1 | | 2 | filename1 | 3 | | 4 | | ="\\Year 2004\September\["_B2_".xls]sheet1'!$A$1 will not work -- yee ve ----------------------------------------------------------------------- yee ven's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1479 View this thread: http://www.excel...

relative reference to previous sheet in formulas?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello. <br><br>the simplest way to ask this seems to be to ask the question before wasting anybody's time on details. <br><br>is this possible? <br> =('previous sheet relative to this one'!H22) <br><br>the longer version is this: <br> I have a worksheet that will grow from one sheet to 52 sheets as the weeks of the year roll by. <br><br>I will need to reference a cell in the previous week's worksheet as a starting point in the current ...

Adding Blank Rows after the last Detail of the Report
Hi All, I've used this code from Dwayne: Private Sub Report_Page() Dim intNumLines As Integer Dim intLineNumber As Integer Dim intTopMargin As Integer Dim ctl As Control Dim intLineHeight As Integer intNumLines = 12 intTopMargin = Me.Section(3).Height intLineHeight = Me.Section(0).Height For Each ctl In Me.Section(0).Controls For intLineNumber = 0 To intNumLines - 1 Me.Line (ctl.Left, intTopMargin + _ (intLineNumber * intLineHeight)) - _ Step(ctl.Width, intLineHeight), , B Next Next End Sub My report needs to print blank rows (at least...

Adding hyperlinks with parameters
Hey everybody I have a batch file which needs to be run from an excel sheet with a parameter, like this: c:\1.bat myParameter But when i try to run the Hyperlink, the Excel shows an error message saying it can't open the specified file (probably because the is a space between the file and the parameter) is there any known way to run the link from the excel? On Mar 2, 12:45=A0pm, Pasha <pavel_v...@mailto.mod.gov.il> wrote: > Hey everybody > I have a batch file which needs to be run from an excel sheet with a > parameter, like this: > c:\1.bat myParameter > But when i ...

Automatically Update Date/Time in Template
I can insert a date/time in a new message if I am using Word as my editor, but then I cannot save it as a template (why you would want an auto-updating field in a message that cannot be stored as a template is another question). If I switch off Word as the editor so that I can save as a template, I cannot insert a date/time and have it auto-update. Is there a way out of this conundrum, or should I just chalk it up to another peculiarity of MS software? Thanks for any assistance. ...

Can v4.0 workflow determine what date today is?
My client wants an e-mail to be sent to the record owner if the Closed Date on the Opp is past. The problem here is that in order to know that today is 1 day past when the Closed Date was projected, I actually need WF to know what today is. I can’t figure out how to do that. Workflow knows all the date attributes for the entity and knows the date it was fired, but I can’t figure out how it can tell what today is, and therefore determine if today is greater than a stored date. see if it can =B4be some of help. http://mscrmsupport.wordpress.com/2007/11/20/comparing-crm-datetime-with-...

excel formulas #2
Can someone help me with how to create a formula that looks at one cell and based upon the number in that cell (if statement) then gives me another number? example: If cell c4 contains the number 1; then how can I have cell a8 look at cell c4 and give me the number 1 (other than a link) / then have cell a9 look at cell c4 if it contains the number 2 to then give me the number 2.................... I need a formula to look at a cell that will contain a number from 1 to 5 or above and then accoridingly give me a coresponding number to reflect if that number is 1,2,3,4 or 5. Each cel...

Make A Set Of Date
i have 2 sheets FOGLIO1 e FOGLIO2 in FOGLIO1 cell D2 i have the value 60 in the cell J2 i have a date, for example 27/11/2004, i colud want fill a set of date in FOGLIO2 to start in the cell G2 for every 60 month day year, for example: sheet FOGLIO2 in the cell G2 27/11/2004 in the cell G3 27/12/2004 in the cell G4 27/01/2005 in the cell G5 27/02/2005 .... in the cell BN2 27/10/2009 this macro for every data present in the column D2:D2000 and in the column J2:J2000 from FOGLIO1 when the actual date is the same in the cell G2 of sheet FOGLIO2 delete the cell G2 and skip to left the other c...

Date Issue
Could someone please help me. I have attached my problem, on the click of a button, called "Date Box" i have a userform appear. i then have a textbox and a button. I would like the text box to be autopopulated with today's date, an the button to open a calandar so that this date can be changed i requested. my coding is very poor, so thats why i am asking here..... Any help would be appreciated. Cheers And +---------------------------------------------------------------- | Attachment filename: date issue.zip |Download attachment: http...

Incorrect received date
Hello, My domain controller server failed and it's systemboard was replaced by the manufacturer. We did not anticipate the date/time - Kerberos sensitivity and all systems were disconnected until we were able to synchornize the time (new server was off). Unfortunately, we were so focused on the time that did not noticed the date being off to. About 12 hrs later a user reported receiving message's with "tomorrow's" date. How bizarre? We checked the newly repaired server and there was the culprit. Upon fixing the date and resynchronizing/replicating Active Director...

outlook vcard, ad integration user properties
my boss is trying to get hix fax# in his vcard. he is using his address from the global address list I have modified his profile in active directory users and computers: I go into AD users and computers, double click on his user, go to the telephones tab, and enter his fax #. but when I did this his fax # is still not being pulled up in his outlook vcard. Does anyone know where outlook is trying to get this field from / how i can get this field into his vcard? thx ...

lost date pull down money 2001
how do i get the date pull down to work again in money 2001 register ...

Why formula doesn't change on column insertion?
Hello: I know I will feel stupid when I hear the answer, but... If I have a formula ( =average(a1:a5) ), and I insert a new column, the formula, being relative, changes to ( =average(a1:a6) ). This works fine in a test. BUT I have a working spreadsheet in which inserting a column does *not* change the relative formula. Why? I can't see anything different in my working spreadsheet... -- Fred Boer Never mind - resolved. -- Fred Boer "Fred Boer" <fredboer1@NOyahooSPAM.com> wrote in message news:uin$VfrDIHA.5160@TK2MSFTNGP05.phx.gbl... > Hello: > >...

Coping Formulas
I have put a conditional format formula to high light duplicate entries. The problem is for each cell it has changed the range values in the formula. In order for it to work I need the ranges to stay the same as in the first cell. It works if I manually go to each cell and change the ranges in the conditional format, but there are a lot of rows and I don't want to go to each one. Thanks for any help. Chance hi, try this go to the cell which contains the correct formula for your conditional formatting, in the conditional formatting dialog window change the cell reference to fo...

ADDING A NUMBER
how do i add a number to a range of cells? I need to add 11.27 to a number of different cells each with a different number in them Put 11.27 in an empty cell, format it the same way as the numbers you want to add to, copy it, then select the numbers you want to add to and do edit>paste special and select add. -- Regards, Peo Sjoblom "Helpme" <Helpme@discussions.microsoft.com> wrote in message news:6D51344A-78E6-4377-B292-FC6C16FF6BA2@microsoft.com... > how do i add a number to a range of cells? I need to add 11.27 to a > number > of different cells e...

Search by date
Hello, I am trying to search a table by date using a combo box. I want the combo box to be populated by dates from a table, however many of the dates are listed in the table more than once. Is there a way to only display the dates in the combo box once? Thanks SELECT DISTINCT [SOME_DATE] FROM SomeTable; -- Dave Hargis, Microsoft Access MVP "wesley.allen@gmail.com" wrote: > Hello, > > I am trying to search a table by date using a combo box. I want the > combo box to be populated by dates from a table, however many of the > dates are listed in the table more ...

adding new records
I am totally new to CRM, so I hope this is really easy question: This concerns permissions.. I have a "sales" group, and with one of those people i need to be able to let them add new records on behalf of other "sales" people. Is there like a standard permission setting for it? (obviously every "sales" person can add new records if they select them selfs as the owner) thanks, Michal. Hi, By default the person who creates the record will be the owner. After record creation the person could reassign the record (manually) to another user. If all rec...

When adding attachments...
....in Outlook the following errors comes up: "Out of memory or system resources, please close some programs and try again" Quotas are fine. I have deinstalled/reinstalled Office, cleaned 'temp' areas, and many other tidbits. Any other ideas? Thanks. This might help as i had this problem with Outlook 98. Is the file you are trying to copy to attach nested in sub folders? if so try and copy the attachment to a root drive like c:\ Ignore previous email. wrong spellin if the attachment is nested in sub folders(this means you have to open different folders to get to ...

Picture Disappears When Added To Contact In Outlook 2003
Can someone help please. When I try to attach a picture to a contact in outlook 2003 immediately I double click the picture to be attached it disappears out of the outlook contact along with the placement holder in the form. Only when I right click the mouse and go remove picture do I get the placement holder back. I have looked at all the help menus but they offer no clues. Thanks TS ...

create a new worksheet when a cell has a date entered
I am trying to create a new worksheet when a cell has a date entered into it For instant in Worksheet 1 you type into cell B11 a date I would like to automaticly create a new worksheet and call it that date entered to B11 Then if I type a Date into cell B12 a new worksheet would be created and named the date entered into B12 and so on Is this out of the question See response in your other post. "Zane" wrote: > I am trying to create a new worksheet when a cell has a date entered > into it > > For instant in Worksheet 1 you type into cell B11 a date > I would l...

Adding ActiveX to dialog with wrapper
When I add an ActiveX object to a dialog using the dialog editor - right click - insert activeX object, the help on the resulting activex selection tool states that a wrapper will not be added by using this method. If you need a wrapper, it suggests using the class view. This is using Visual Studio 2005. I'm having some problems with this on an existing project. To make sure I understand the process, I created a new MFC project, added a dialog, created a class for that dialog and added a Flexgrid object using the right click method in the dialog. I then added a variable for the flexgrid...

Adding alternate columns
I work on a schedule where I record ticket count and ticket sales for each show, listed chronologically. Column A = week ending date Column B = # Tickets sold for April 5 show Column C = Ticket Revenue for April 5 show Column D = # Tickets sold for April 9 show Column E = Ticket Revenue for April 9 show Column F = # Tickets sold for April 12 show Column G = Ticket Revenue for April 12 show Column H = Total # Tickets sold for all shows Column I = Total Ticket Revenue for all shows In the Totals columns, the Total Tickets formula is "+B4+D4+F4", and the Total Revenue f...

TEXT(date)?
As you know, the function =DATEVALUE("8/22/2008") produces a date from its corresponding text representation. I want to go the OTHER way. That is, I want to take a date and produce its text representation (I'll settle for any reasonable format). How might I go about doing that? Thanks, John Your answer is in your question!! try: =TEXT(TODAY(),"dd-mm-yy") or any other reasonable date-style format -- Gary's Student "JMF" wrote: > As you know, the function > > =DATEVALUE("8/22/2008") > > produces a date from its corr...

Adding more than three Conditions to 'Conditional Formatting'
Can i add more than three conditions to the conditional formatting presets using code? Regards [Riz] -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=320382 Riz, as you have found out, only 3 conditions with conditional formatting. But yes you can use some code to get more than 3, try this it will change the cell color in column A when you put in one ,two, three, or four, right click on the she...