Question about using SUM with a date criteria

Hello,

I'm using the following array formula (Thank you again Macropod!) to add 
Sick/Vacation/Personal leave hours (as simple numbers) in cells that also 
contain text:

=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" 
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))

Is there a way to add a date criteria to this formula so that it will only 
add those hours as of the current date?  In the spreadsheet, Column A 
contains dates.

Any help is appreciated.

Thanks,

Mike 

0
mslater518 (22)
10/18/2007 9:09:27 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
368 Views

Similar Articles

[PageSpeed] 42

You should tell us more about what your data looks like
Meanwhile look into SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Michael Slater" <mslater518@comcast.net> wrote in message 
news:A-6dnbHNh7gdVoranZ2dnUVZ_qygnZ2d@comcast.com...
> Hello,
>
> I'm using the following array formula (Thank you again Macropod!) to add 
> Sick/Vacation/Personal leave hours (as simple numbers) in cells that also 
> contain text:
>
> =SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" 
> ",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
>
> Is there a way to add a date criteria to this formula so that it will only 
> add those hours as of the current date?  In the spreadsheet, Column A 
> contains dates.
>
> Any help is appreciated.
>
> Thanks,
>
> Mike 


0
bliengme5824 (3040)
10/19/2007 12:59:32 AM
Try

=SUM(IF(A1:A4=TODAY(),LEFT(0&B1:B4,FIND(" ",B1:B4&" "))+0))

confirmed with CTRL+SHIFT+ENTER

"Bernard Liengme" wrote:

> You should tell us more about what your data looks like
> Meanwhile look into SUMPRODUCT
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> http://mcgimpsey.com/excel/formulae/doubleneg.html
> 
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
> 
> "Michael Slater" <mslater518@comcast.net> wrote in message 
> news:A-6dnbHNh7gdVoranZ2dnUVZ_qygnZ2d@comcast.com...
> > Hello,
> >
> > I'm using the following array formula (Thank you again Macropod!) to add 
> > Sick/Vacation/Personal leave hours (as simple numbers) in cells that also 
> > contain text:
> >
> > =SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" 
> > ",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
> >
> > Is there a way to add a date criteria to this formula so that it will only 
> > add those hours as of the current date?  In the spreadsheet, Column A 
> > contains dates.
> >
> > Any help is appreciated.
> >
> > Thanks,
> >
> > Mike 
> 
> 
> 
0
10/21/2007 3:45:01 PM
Thank you for your suggestions!  Daddylonglegs, I was not able to get your 
formula to work, however, I did figure out how to modify the original formula 
I was using:

=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" 
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))

Modification:

=SUM(IF(A1:A4<=TODAY(),IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" 
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1)))))

Again,  Thanks for all your help!

Mike



"daddylonglegs" wrote:

> Try
> 
> =SUM(IF(A1:A4=TODAY(),LEFT(0&B1:B4,FIND(" ",B1:B4&" "))+0))
> 
> confirmed with CTRL+SHIFT+ENTER
> 
> "Bernard Liengme" wrote:
> 
> > You should tell us more about what your data looks like
> > Meanwhile look into SUMPRODUCT
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> > 
> > -- 
> > Bernard V Liengme
> > Microsoft Excel MVP
> > www.stfx.ca/people/bliengme
> > remove caps from email
> > 
> > "Michael Slater" <mslater518@comcast.net> wrote in message 
> > news:A-6dnbHNh7gdVoranZ2dnUVZ_qygnZ2d@comcast.com...
> > > Hello,
> > >
> > > I'm using the following array formula (Thank you again Macropod!) to add 
> > > Sick/Vacation/Personal leave hours (as simple numbers) in cells that also 
> > > contain text:
> > >
> > > =SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" 
> > > ",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))
> > >
> > > Is there a way to add a date criteria to this formula so that it will only 
> > > add those hours as of the current date?  In the spreadsheet, Column A 
> > > contains dates.
> > >
> > > Any help is appreciated.
> > >
> > > Thanks,
> > >
> > > Mike 
> > 
> > 
> > 
0
10/25/2007 2:31:03 PM
Reply:

Similar Artilces:

Concatenation question
I am trying to concatenate two fields and then add text in brackets to the end. Here is what I have: StaffFullName = ([Me.LastName] & ", " & [Me.FirstName] & " (Mgr)") I want the end result to be: Smith, Mary (Mgr) How do I achieve this? Thank you, Terri What you've done will work. You can also use the "+" operator if there is a possibility that there will be a missing first name: StaffFullName = ([Me.LastName] & "," & (" "+ [Me.FirstName]) & " (Mgr)") That will avoid an extra spa...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific words in the e-mail header. I set up this rule to search for sober.worm since I'm receiving a copious amount of spam e-mails which include the sober.worm attachment. I set the rule to delete the e-mails indefinately but it does not delete it. It just sends the e-mails to the junk mail folder. I would like to have it delete the e-mails forever. What can I do to achieve this? -- Nocturnal @ http://www.randomfix.com Also, I did get it to work a few times but the rule stops working. Another thing is if ...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...

Outlook Question
What causes url links not work? I'm experiencing problems with it, and cannot get them to show the URL Links. Clicking the Links doesn't help. ...

Option trades with past expiry dates not showing up in Portfolio M
Hello: Money 2006 Portfolio Manager does not show closed option trades that have expiry dates in the past, even when the "show closed positions" is checked. The transactions are still there in the investment accounts, just not visible in portfolio manager. I just called microsoft support and have notified them of the issue and hope that this issue is fixed as an update. If not, it pretty much makes the portfolio manager (and Microsoft Money 2006) useless for option trading investors. "MumbaiBabu" <MumbaiBabu@discussions.microsoft.com> wrote in message news:1...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using excel. You could use a helper column, with something like = A2. Format this (Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals, "At each change in: Month" and "Use function: Count." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" <Brewisc13@discussions.microsoft.com> wrote in message news:44E02AAC-8216-43F5-846F-E981E978E44B@microsoft.com... > We are trying to find out how many birthdays fall with in...

Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query, the results come back with multiple lines of data for an order because of multiple critereas in another field. Here is an example of the data returned to my query: Order Status A In Process A Sent B In Process C In Process I would like to see only the data for orders that have not been shipped, and totally exclude data for orders that have been sent. In the example above, I would like my query results to show Orders B and C, but no data for A since it has al...

vb date and ssn textbox formats
Ok, so maybe I am crazy...but I have checked "Visual Basic 6 how t program", and "Visual basic 5 teach yourself in 24 hrs" for and exampl or how to format a text box on a user form to accept and display date and Social Security Numbers in the specified format. Thanks Al -- Message posted from http://www.ExcelForum.com Date TextBox1.Text = Format(Date,"dd mmm yyyy") SSNs TextBox1.Text = Format(mySSN, "000-00-0000") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mai...

Two calendar dates in one cell..??
I have a Microsoft Publisher calendar document that was created in 2005 that shows the dates Monday - Sunday. When I print it, it will not print out any dates that fall after the fifth week (eg January 2006 it prints up to January 29th but not the 30th or 31st). These are in an "overflow" area. But when I open the calendar part in Word to edit the calendar, it appears normally and that sixth week shows up just fine. I need that sixth week to print OR I need to do the split date feature (I would prefer the split date). How can I set up the numbers in one of those cells so i...