SumProduct with date and time

I have finally found that with my formulas the calulations are based
on the time.  Can someone help me find the best way to correct this
without using macros to strip the time out of the data.

Bob	09/08/2007 01:05	13/08/2007 00:00	Bob
Bill	10/08/2007 23:39		Bill
Kyle	11/08/2007 07:36		Kyle
Bob	16/07/2007 20:39
Bill	27/07/2007 19:59
Kyle	18/07/2007 16:51
Bob	14/07/2007 16:31
Bill	21/06/2007 16:46
Kyle	30/06/2007 16:55
Bob	12/06/2007 01:05
Bill	13/06/2007 23:39			1	2	0	1	1
Kyle	14/06/2007 07:36			1	1	1	1	1
Bob	12/01/2007 20:39			1	1	2	0	1
Bill	13/08/2006 19:59
Kyle	14/05/1999 16:51

These are the formulas for the above calcs
SUMPRODUCT(--($A$1:$A$15$D$1),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-90))
SUMPRODUCT(--($A$1:$A$15$D$2),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-90))
SUMPRODUCT(--($A$1:$A$15$D$3),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-90))




Bob	09/08/2007	13/08/2007	Bob
Bill	10/08/2007		Bill
Kyle	11/08/2007		Kyle
Bob	16/07/2007
Bill	27/07/2007
Kyle	18/07/2007
Bob	14/07/2007
Bill	21/06/2007
Kyle	30/06/2007
Bob	12/06/2007
Bill	13/06/2007			1	1	1	1	1
Kyle	14/06/2007			1	1	1	1	1
Bob	12/01/2007			1	1	1	1	1
Bill	13/08/2006
Kyle	14/05/1999

These are the formulas for the above calcs
SUMPRODUCT(--($A$25:$A$39$D$25),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D$26),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D$27),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-90))

0
frogman71 (34)
8/17/2007 10:22:43 PM
excel 39879 articles. 2 followers. Follow

4 Replies
617 Views

Similar Articles

[PageSpeed] 5

If you want the dates only from a range where there are both dates and times


--(INT($B$1:$B$15)<$C$1)

will strip off the time


to strip off the dates to compare times only


--(MOD($B$1:$B$15,1)<$C$1)





-- 
Regards,

Peo Sjoblom





"frogman7" <frogman7@googlemail.com> wrote in message 
news:1187389363.659412.65760@r23g2000prd.googlegroups.com...
>I have finally found that with my formulas the calulations are based
> on the time.  Can someone help me find the best way to correct this
> without using macros to strip the time out of the data.
>
> Bob 09/08/2007 01:05 13/08/2007 00:00 Bob
> Bill 10/08/2007 23:39 Bill
> Kyle 11/08/2007 07:36 Kyle
> Bob 16/07/2007 20:39
> Bill 27/07/2007 19:59
> Kyle 18/07/2007 16:51
> Bob 14/07/2007 16:31
> Bill 21/06/2007 16:46
> Kyle 30/06/2007 16:55
> Bob 12/06/2007 01:05
> Bill 13/06/2007 23:39 1 2 0 1 1
> Kyle 14/06/2007 07:36 1 1 1 1 1
> Bob 12/01/2007 20:39 1 1 2 0 1
> Bill 13/08/2006 19:59
> Kyle 14/05/1999 16:51
>
> These are the formulas for the above calcs
> SUMPRODUCT(--($A$1:$A$15$D$1),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
> $1-15))
> SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
> $1-30))
> SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
> $1-60))
> SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
> $1-90))
> SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-90))
> SUMPRODUCT(--($A$1:$A$15$D$2),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
> $1-15))
> SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
> $1-30))
> SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
> $1-60))
> SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
> $1-90))
> SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-90))
> SUMPRODUCT(--($A$1:$A$15$D$3),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
> $1-15))
> SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
> $1-30))
> SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
> $1-60))
> SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
> $1-90))
> SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-90))
>
>
>
>
> Bob 09/08/2007 13/08/2007 Bob
> Bill 10/08/2007 Bill
> Kyle 11/08/2007 Kyle
> Bob 16/07/2007
> Bill 27/07/2007
> Kyle 18/07/2007
> Bob 14/07/2007
> Bill 21/06/2007
> Kyle 30/06/2007
> Bob 12/06/2007
> Bill 13/06/2007 1 1 1 1 1
> Kyle 14/06/2007 1 1 1 1 1
> Bob 12/01/2007 1 1 1 1 1
> Bill 13/08/2006
> Kyle 14/05/1999
>
> These are the formulas for the above calcs
> SUMPRODUCT(--($A$25:$A$39$D$25),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
> $25-15))
> SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
> $C$25-30))
> SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
> $C$25-60))
> SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
> $C$25-90))
> SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-90))
> SUMPRODUCT(--($A$25:$A$39$D$26),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
> $25-15))
> SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
> $C$25-30))
> SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
> $C$25-60))
> SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
> $C$25-90))
> SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-90))
> SUMPRODUCT(--($A$25:$A$39$D$27),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
> $25-15))
> SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
> $C$25-30))
> SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
> $C$25-60))
> SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
> $C$25-90))
> SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-90))
> 


0
terre081 (3244)
8/17/2007 10:31:35 PM
On Aug 17, 3:31 pm, "Peo Sjoblom" <terr...@mvps.org> wrote:
> If you want the dates only from a range where there are both dates and times
>
> --(INT($B$1:$B$15)<$C$1)
>
> will strip off the time
>
> to strip off the dates to compare times only
>
> --(MOD($B$1:$B$15,1)<$C$1)
>
> --
> Regards,
>
> Peo Sjoblom
>
> "frogman7" <frogm...@googlemail.com> wrote in message
>
> news:1187389363.659412.65760@r23g2000prd.googlegroups.com...
>
>
>
> >I have finally found that with my formulas the calulations are based
> > on the time.  Can someone help me find the best way to correct this
> > without using macros to strip the time out of the data.
>
> > Bob 09/08/2007 01:05 13/08/2007 00:00 Bob
> > Bill 10/08/2007 23:39 Bill
> > Kyle 11/08/2007 07:36 Kyle
> > Bob 16/07/2007 20:39
> > Bill 27/07/2007 19:59
> > Kyle 18/07/2007 16:51
> > Bob 14/07/2007 16:31
> > Bill 21/06/2007 16:46
> > Kyle 30/06/2007 16:55
> > Bob 12/06/2007 01:05
> > Bill 13/06/2007 23:39 1 2 0 1 1
> > Kyle 14/06/2007 07:36 1 1 1 1 1
> > Bob 12/01/2007 20:39 1 1 2 0 1
> > Bill 13/08/2006 19:59
> > Kyle 14/05/1999 16:51
>
> > These are the formulas for the above calcs
> > SUMPRODUCT(--($A$1:$A$15$D$1),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
> > $1-15))
> > SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
> > $1-30))
> > SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
> > $1-60))
> > SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
> > $1-90))
> > SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-90))
> > SUMPRODUCT(--($A$1:$A$15$D$2),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
> > $1-15))
> > SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
> > $1-30))
> > SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
> > $1-60))
> > SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
> > $1-90))
> > SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-90))
> > SUMPRODUCT(--($A$1:$A$15$D$3),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
> > $1-15))
> > SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
> > $1-30))
> > SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
> > $1-60))
> > SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
> > $1-90))
> > SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-90))
>
> > Bob 09/08/2007 13/08/2007 Bob
> > Bill 10/08/2007 Bill
> > Kyle 11/08/2007 Kyle
> > Bob 16/07/2007
> > Bill 27/07/2007
> > Kyle 18/07/2007
> > Bob 14/07/2007
> > Bill 21/06/2007
> > Kyle 30/06/2007
> > Bob 12/06/2007
> > Bill 13/06/2007 1 1 1 1 1
> > Kyle 14/06/2007 1 1 1 1 1
> > Bob 12/01/2007 1 1 1 1 1
> > Bill 13/08/2006
> > Kyle 14/05/1999
>
> > These are the formulas for the above calcs
> > SUMPRODUCT(--($A$25:$A$39$D$25),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
> > $25-15))
> > SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
> > $C$25-30))
> > SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
> > $C$25-60))
> > SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
> > $C$25-90))
> > SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-90))
> > SUMPRODUCT(--($A$25:$A$39$D$26),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
> > $25-15))
> > SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
> > $C$25-30))
> > SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
> > $C$25-60))
> > SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
> > $C$25-90))
> > SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-90))
> > SUMPRODUCT(--($A$25:$A$39$D$27),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
> > $25-15))
> > SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
> > $C$25-30))
> > SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
> > $C$25-60))
> > SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
> > $C$25-90))
> > SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-90))- Hide quoted text -
>
> - Show quoted text -

It work but when i apply it to my real data on a seprate sheet i get a
value error.
=SUMPRODUCT(--(INT('All Open USA Assigned'!$D$1:$D$9999)=$A45),--
(INT('All Open USA Assigned'!$AW$1:$AW$9999)>$B$1-15),--(INT('All Open
USA Assigned'!$AW$1:$AW$9999)<=$B$1))
What is wrong with this formula?

0
frogman71 (34)
8/17/2007 11:42:53 PM
Do you have any text in D1:D9999 or AW1:AW9999.

=int(somecellwithtext) 
will return that #value! error

Do you have any errors (#value!'s) in any of those cells?  Those errors will
make this formula return an error, too.

frogman7 wrote:
> 
<<snipped>>
> 
> It work but when i apply it to my real data on a seprate sheet i get a
> value error.
> =SUMPRODUCT(--(INT('All Open USA Assigned'!$D$1:$D$9999)=$A45),--
> (INT('All Open USA Assigned'!$AW$1:$AW$9999)>$B$1-15),--(INT('All Open
> USA Assigned'!$AW$1:$AW$9999)<=$B$1))
> What is wrong with this formula?

-- 

Dave Peterson
0
petersod (12005)
8/18/2007 4:31:24 AM
On Aug 17, 9:31 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Do you have any text in D1:D9999 or AW1:AW9999.
>
> =int(somecellwithtext)
> will return that #value! error
>
> Do you have any errors (#value!'s) in any of those cells?  Those errors will
> make this formula return an error, too.
>
>
>
> frogman7 wrote:
>
> <<snipped>>
>
> > It work but when i apply it to my real data on a seprate sheet i get a
> > value error.
> > =SUMPRODUCT(--(INT('All Open USA Assigned'!$D$1:$D$9999)=$A45),--
> > (INT('All Open USA Assigned'!$AW$1:$AW$9999)>$B$1-15),--(INT('All Open
> > USA Assigned'!$AW$1:$AW$9999)<=$B$1))
> > What is wrong with this formula?
>
> --
>
> Dave Peterson
That was it i had the header row in the formula.

0
frogman71 (34)
8/18/2007 2:40:58 PM
Reply:

Similar Artilces:

automatically add date to email #2
Is there a way to add a date code to an email so it automatically grabs the date from the computer system. Thanks, ddlaz The header/properties of the msg will have both date and time appended, or are you saying you want to add the date to the body of your msg? "ddlaz" <ddlaz@comcast.net> wrote in message news:5uudnfiurtYf9PzfRVn-sw@comcast.com... > Is there a way to add a date code to an email so it automatically grabs the > date from the computer system. > Thanks, ddlaz > > Will setting the date in the header allow the date to automatically display eac...

change date in a sql statement from a cell
Hello, I am using Excel 2007 I have a pivot table that gets refresh everyday. The data from the pivot table is based on a sql statement, which the data is connected to a AS/400 table. Here is my problem every morning I go in the connection properties and change the SQLstatement (date) in the command text. I don't want my user to do this. What other option can I do? I was thinking change the date in a cell (A1) and somehow the SQL statement picks up the new date or maybe some sort of parameter, but I am clueless in how to do this. Any tips or website to visit I will a...

sales line item workflow and process holds and requested ship date
It would be nice if we could set up workflow tracking per line item as well as assign process holds per line item in Sales Trx Entry. It would also be nice to have the abilility to fulfill all items on an order through a specified requested ship date and leave items with future requested ship dates unfulfilled. Then to be able to transfer and ship items that have been fulfilled without having to fulfill those with future requested ship dates. Right now I have to backorder those items that have future requested ship dates just so I can transfer and fulfill the other items on the order...

Convert SUMPRODUCT to COUNT
I have a formula where I need to count instead of sum. Is there a way to do this? Joe =SUMPRODUCT((YEAR('2003-2004'!$A$4:$A$10000)=(Summary!$B$4))*(MONTH('2003-20 04'!$A$4:$A$10000)=(Summary!$D$3))*(('2003-2004'!$I$4:$I$10000)=E4)*'2003-20 04'!$E$4:$E$10000) I tried: {=SUM((MONTH('2003-2004'!$A$4:$A$10000)=(Summary!$D$3)*(YEAR('2003-2004'!$A$ 4:$A$10000)=(Summary!$B$4)*(('2003-2004'!$I$4:$I$10000)=(Summary!E4)))))} Hi Just take out the bit that is the summing part. If it's '2003-2004'!$E$4:$E$10000, just drop it off...

Time Convert
Hello, I have a database that I have been querying from my visual basic 6 program to take my access DB and convert it to excel. I use a multitude of SQL queries to get it to the format I desire, but i have a time column that has both the date and time within them. When I open the excel sheet, I only get the date displayed but the time is there I just have to reformat the column. Is there a way to query my Access database in such a way I only have the time and therefore wont see the date? Basically without knowing exactly what Im typing i have something as follows (which obviously doesnt wo...

Adding times
I am using Excel 97 I have a column that has a list of time in hours and minutes, 34:12 (thirty foru hours and 12 minutes). How can I add these and also do an average. I would like to be be able, forsinstance to add 12:34 plus 23:52 minutes and get 36:26. HELP Also need to average this as well. Linda Linda just add the cells and format the result as Custom [hh]:mm Regards Trevor "Linda" <anonymous@discussions.microsoft.com> wrote in message news:00ff01c3fa5d$0da474d0$a001280a@phx.gbl... > I am using Excel 97 > I have a column that has a list of time in hours a...

Calculating Time Difference
Hi, Searched previous postings but can't find an answer to my problem - hope someone can help. I have two columns both in hh:mm format; first shows a start time and second shows a stop time. I want a third column to show the difference in hours and minutes between the two times - if I can't have this I will accept difference in minutes only between the two times. Best I have come up with is the calculation =ABS(a2-a1) and custom format the cell to show minutes only; [mm]. There seems to be a problem with this - the calculation does not always return the expected time differe...

Text Field Validation Rule for Date
Hi, I have a RESPONSE field on my table. This field is set up as a text field (I do not want to change it due to specific requirements)- however, I'd like to set up a validation rule to determine whether or not it is a valid date: mm/dd/yyyy. What should I input in the validation rule? Thanks I do not think it is possible at table level but in your data entry form us AfterUpdate event and IsDate function. -- Build a little, test a little. "shm135" wrote: > Hi, > > I have a RESPONSE field on my table. This field is set up as a text > fiel...

Calculating Dates, PlEASE HELP! THANKS!
Ok, If I want to calculate the difference of a date with the following date in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value in B1 of 35. How do I do this? Thanks! In Excell the formula looks like this: =IF(+A1-A2>0,A1-A2,0) Is this an Access question? If so, then post you table and field names with datatype, sample data, and what you want the results to look like. -- Build a little, test a little. "kritter0021" wrote: > Ok, If I want to calculate the difference of a date with the following date > in Access: ...

need some help with: formatting of x2 dates in 1 cell
Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not...

MF_GRAYED for menu items (run time)
HI. I would like to enable and able some of my menu items in run time. Here is the code I have been writing, but it seems not to work, as nothing is gonna happen: BEGIN_MESSAGE_MAP(CTestGLApp, CWinApp) //... ON_COMMAND(ID_REMOVE, OnRemoveData) //... END_MESSAGE_MAP() void CTestGLApp::OnRemoveData () { CSelectDlg SelectDlg; SelectDlg.DoModal(); MyGlobDat.erase(MyGlobDat.begin() + SelectDlg.GetIndex()); if(!MyGlobDat.size()) { CWnd *wind; wind = AfxGetMainWnd(); CMenu *menu = wind->GetMenu(); menu->EnableMenuItem(ID_REMOVE,MF_GRAYED); } } Thanks a lot for your help! ...

Text with Dates
in cell A1 i have some text. in cell B1 i have a date. how do I get Excel to display both the text and the date. I've tried: Dim olDate As Range Dim olText As Range Set olDate = Sheet1.Cells.Item(1,"A") Set olText = Sheet1.Cells.Item(1,"B") ..Subject= olText +" - " + olDate I was hoping to get something like "text - 6/9/05" in the subject bar, but everytime I do this i get a run time error. i've tried replacing the date with text and it works just fine, and i've also tried just the date cell and it works fine. the problem comes up when i...

Only one user at a time can access OWA
Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at any one time. As soon as the first user logs out the next can then log on, but in a organisation of 1300 - bit of a problem. Previously we did have things working fine with Exchange 5.5 on WinNT but a hacking attack put paid to that system! Have run through MS trouble shooting but found no mention of this problem anywhere else. Any suggestions welcomed Regards Mike Cameron cameronm@queensbury.beds.sch.uk (Mike Cameron) wrote: >Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at >any one time. As...

Problem with date in user form
Hello, I create user form for database that contain dates in one column. But when I fill user form it copy the date in the database column like text and the formulas related to this cells dosen't work.The cells in the column are formated like dates. How to solve this problem? Ivo Ivo, Use DateValue to convert the text to a date, like: DataBaseCell.Value = DateValue(me.TextBox1.Text) HTH, Bernie MS Excel MVP "Ivo_69" <Ivo_69@discussions.microsoft.com> wrote in message news:DA7115AD-3107-4151-AF21-1089D3C30AF3@microsoft.com... > Hello, > I create user form f...

Date Format Changed when convert to Excel
Dear All, there is a problem that when i convert the text file to excel then date formart for the first 12 dates have been changed with "/" sepreator and other remains with "-" that creates a lot of problem please help to reslove the issue. On Wed, 19 Sep 2007 22:27:06 -0700, Ather <sohail.ather@paktel.com> wrote: >Dear All, > >there is a problem that when i convert the text file to excel then >date formart for the first 12 dates have been changed with "/" >sepreator and other remains with "-" that creates a lot of problem >pl...

Print 12/1/1 in cell (Excel changes it to date or another #
While trying to formulate a magazine order list, Excel changes the number of magazines needed (12/1/1) to a number other than that typed into the cell. What I need is the number 12 (for English), 1 (for MP3) and 1 (for Finnish) in this particular cell. How do I get it to print the actual numbers? try putting a ' infront of the numbers "Say Goodnight, Gracie" wrote: > While trying to formulate a magazine order list, Excel changes the number of > magazines needed (12/1/1) to a number other than that typed into the cell. > What I need is the number 12 (for Engli...

date #5
Hi Peeps Many thanks in advance for any help/ advice that you can give me I am trying to construct a simple database in Excel to track sales and payments made Is there a way to use conditional formatting/or any other tool to highlight payment due not yet received by referecing it to the date on my computer i.e the date on my computer is 6th June 2004, payment was due 30th May 2004, I would like the cell that has the due date to be highlighted Best Regards Yinka Yinka Format>Conditional Formatting>Cell Value is: less than =TODAY() Gord Dibben Excel MVP On Sat, 5 Jun 2004 14:51:02 ...

subtract date in access to get age
I have forgotton how to subtract 2 dates in acces to get the age, has anyont got a quickie for this?? cheers See Chip Pearson's DATEDIF page. http://www.cpearson.com/excel/datedif.htm Gord Dibben Excel MVP On Thu, 14 Oct 2004 16:39:02 -0700, ian <ian@discussions.microsoft.com> wrote: >I have forgotton how to subtract 2 dates in acces to get the age, has anyont >got a quickie for this?? > >cheers Gord Dibben <gorddibbATshawDOTca> wrote ... > > I have forgotton how to subtract 2 dates in acces to get the age > > > See Chip Pearson's DATED...

Account Activity Blackout Dates
Hi, My group is using CRM, and we need the ability to add blackout dates to our accounts. That is, we need to be able to prohibit certain accounts from being scheduled for an activity during particular time periods. Does CRM have any built-in functionality that allows for this? Thank you! -Dave- Afraid not. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 2 Feb 2005 11:20:27 -0800, "Dave" <renor321@yahoo.com> wrote: Hi, My group is using CRM, and we need the ability to add blackout dates to our accounts...

report by date 02-19-08
->Hi, ->My project is in MS Access 2002. ->In that I have one form which I am using to generate report by date. ->In that form I have two three fields StartDate,EndDate,ItemNo all are of type [B]Text[/B]. ->Now when I select StartDate and EndDate both of same Year then its working properly. ->But if I select StartDate from Previous Year and Today's Date as EndDate then it will simply show results of current year and only of StartDate. ->Means if i enter StartDate=1/1/2007 and EndDate=02/19/2008 then it will show all results of current year(1/1/2008-02/19/2008) and resu...

Code efficiency of Sumproduct()
Two formulae generating the same result: {=SUM(array1*array2*...arrayn)} =SUMPRODUCT(array1*array2*...arrayn) Does the non-array version use up fewer PC resources and/or calculate faster than the array version? Or do they both compile the same way? -- Return email address is not as DEEP as it appears Hi use =SUMPRODUCT(array1,array2,...,arrayn) This should be faster than the other two versions -- Regards Frank Kabel Frankfurt, Germany Jack Schitt wrote: > Two formulae generating the same result: > {=SUM(array1*array2*...arrayn)} > =SUMPRODUCT(array1*array2*...arrayn) >...

Subform causes Main Form to show the same record multiple times
I have a form named Patients Info with a subform on it for Household Members. The Patients Info form is created from the table: Patients and the Household Members subform is created from the table:FedPovertyLevel. The table Patients is related to the table FedPovertyLevel in a one to many relationship. The problem I have is that when a second, third, etc Household Member is added, the button for the record selector on the main form now has to be clicked forward by the number of household members. For Example, if only one name is entered as a Household member, there is only ...

add minutes to a given time
Hey there, in cell A2 I have a time, say 07:00 hrs. In B1 in have i.e. 3 minutes of time an operation takes. Now I want to add those 3 minutes to the time in A2, so I have 07:03 hrs in cell B2 How do I have to format the cells to get that done? Thanx well in advance! Jochen Hi Jochen, In B2 put =A2+B1/24/60 and format cell as [h]:mm HTH Martin In A2, enter: 07:00 in B1, enter: 00:03 in B2, enter: =A2+B1 format all those cells as hh:mm HTH -- AP "Jochen Munzinger" <external.jochen.munzinger@de.bosch.com> a �crit dans le message de news: e90105$pba$1@ns2.fe.i...

sumproduct question please
Hi everyone I have a list of names in column a - I then have data in various other columns which is copied from other worksheets (Columns B, F and J are names) I want to sum how many time a name in column a is listed in the others so have entered as: =SUMPRODUCT(($B$2:$B$500=A2)*($F$2:$F$500=A2)*($J$2:$J$500=A2)) But this is not calculating correctly - can someone pls advise what step I'm missing?? -- Thanks as always Lise Try =COUNTIF(A:A,A1)+COUNTIF(F:F,A1)+COUNTIF(J:J,A1) -- Jacob "Lise" wrote: > Hi everyone > > I have a list ...

can TIME values be aggregated?
Hello, Can time values be aggregated? I'm putting together a summary report that will need to show the sum time that something happened. (Employee breaks, for instance.) If break times for 3 employees were "01:00:00", "00:15:00", and "00:12:30" in length, how do I aggregate them to get "01:27:30"? Simply treating them like char values so they can be parsed into integers and added and then divided by 60 doesn't seem like the best way. Thanks, Eric new DBA in '09 wrote: > Hello, > > Can time values be aggregated? ...