Date/time range based calculations

I have a column of values associated with unique dates/times. I need to be 
able to perform different calculations on the column of values based on 
whether or not their associated unique dates/times are between a set of 
date/time ranges (upper and lower limits).

What function should I be using to calculate column C based on the date/time 
ranges?

Example:

           Column A                      Column B                       
Column C
Row 1  Start Time	               03/01/2005 00:29:59	
Row 2  End Time	               03/01/2005 00:59:59	
Row 3  	
Row 4  DATE/TIME	               VALUE	                   CALCULATIONS
Row 5  03/01/2005 00:29:59      1	                   2
Row 6  03/01/2005 00:59:59      1	                   2
Row 7  03/01/2005 01:29:59	2	                   6

c5 = b5*2 because a5 is in the time range of b1:b2		
c6 = b6*2 because a6 is in the time range of b1:b2		
c7 = b7*3 because a7 is not in the time range of b1:b2		

0
jim314 (18)
4/28/2005 4:52:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
586 Views

Similar Articles

[PageSpeed] 30

C5: =IF(AND(A5>=$B$1,A5<=$B$2),B5*2,B5*3)

and copy down

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"jim314" <jim314@discussions.microsoft.com> wrote in message
news:DA0E9852-D437-4F29-867B-C611061145E5@microsoft.com...
> I have a column of values associated with unique dates/times. I need to be
> able to perform different calculations on the column of values based on
> whether or not their associated unique dates/times are between a set of
> date/time ranges (upper and lower limits).
>
> What function should I be using to calculate column C based on the
date/time
> ranges?
>
> Example:
>
>            Column A                      Column B
> Column C
> Row 1  Start Time                03/01/2005 00:29:59
> Row 2  End Time                03/01/2005 00:59:59
> Row 3
> Row 4  DATE/TIME                VALUE                    CALCULATIONS
> Row 5  03/01/2005 00:29:59      1                    2
> Row 6  03/01/2005 00:59:59      1                    2
> Row 7  03/01/2005 01:29:59 2                    6
>
> c5 = b5*2 because a5 is in the time range of b1:b2
> c6 = b6*2 because a6 is in the time range of b1:b2
> c7 = b7*3 because a7 is not in the time range of b1:b2
>


0
bob.phillips1 (6510)
4/28/2005 6:21:56 PM
Reply:

Similar Artilces:

CRM - Sales Pipeline Report
Please help me - I am trying to get the Sales Pipeline report to order correctly on the Estimated Close date. My report formats correctly and I have set to Group by Date from the parent report of the sales pipeline - BUT - when I view the Sales Pipeline Detail report the Estimated Close date is all over the place... E.g. August opportunities following by December then November then October then September! I have spoken to one of my developers and he says that Group by Date is one thing... you need to also order by estimatedclosedate... but he was no help in providing a solution. BTW ...

Find in Named Range problem (2nd Try)
Hello. I'm using Excel X on a Mac (VBA5?) and attempting to write a macro which names a range of text cells I've selected, then searches the cells in that range and bolds any which contain the ">" character (as text, not as "greater than" in a formula). I'm not using conditional formatting because I also want to get rid of the > characters once the bolding is complete. In stepping through the following, I find that it does name the range and does the finding and bolding in a loop. Unfortunately it doesn't stop at the end of the range, but co...

Vb code working one time
Hi, I have a form for adding data to a table. In this form , there is a command button for adding those data and it has the following code: Private Sub Command22_Click() On Error GoTo Err_Command22_Click If IsNull(Me.Combo28) = True Then MsgBox "You Must Select a S.O.Processor First.", vbCritical, "No Processor Selected..." Me.Combo28.SetFocus ElseIf IsNull(Me.CONTRACT_NO) = True Then MsgBox "You Must Input a Contract No. First.", vbCritical, "No Contract No. Input..." Me.CONTRACT_NO.SetFocus ElseIf IsNull(Me.CONTRACTOR) = True Then ...

Every time I open Microsoft Excel or Word my system crashes. Can.
...

Totals based on Iif statement in subreport
Hello, I have some fields in a subreport -- ID, category and amount, that I need to total. For instance, I need a total of "amount" where each category is "200", not including any other cateogry. How do I do this? I Tried it, and it worked when there was only ONE RECORD in that particular ID. If there was a category 200 and another record for that ID that was 203, it's adding the amoutns for both categories together. What am I doing wrong? here is my if statement: =IIf([Category]=200,Sum([Amount]),0) Thank you! MN Mac, Place a calculated field in your ...

problems with imported dates
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I am importing data with dates that range from 1887 to 1950s. Excel keeps the 1880s dates, but either subtracts or adds 4 years and one day to all of the 1900s dates. I have tried reformatting the dates in the source information, reformatting in excel and copying to another spreadsheet, using different date formats to move the data, etc. nothing seems to work. <br><br>If there is anything this program should do, its process dates correctly. This is a major issue when accuracy is involved and is wor...

how do I copy a range to a new sheet
Now that I this group solved my problem of flagging dates of my club members as "expired", how would I automatically copy all of the members that are expired to a new worksheet so I can use this for a mail merge? I would need to check the condition of the expired column, YES or NO, then copy the yes's name and address columns only to a new page. TIA Why not just add that column and when you use mailmerge, just include the records with that flag set the way you need? If you really want to copy the rows, you could apply Data|Filter|Autofilter and filter to show just those mem...

PM
Is there an option to set the default date for the Void Date to the current date vs. the trasaction date? Meaning that I want to use the current date as the posting date instead of the doc date, without having to remember to change it manually (senility). Thanks Jeff G Jeff, There is no option to do this, you have to keep changing the date manually unless you want to have a customization that does this. -- Victoria Yudin GP MVP "Jeff G" <jgarrison@theathletesfoot.com> wrote in message news:uJCMQ8hQGHA.2692@TK2MSFTNGP14.phx.gbl... > Is there an option to set t...

Order Fulfillment Date bug?
When you fulfill an order (ACTIONS->FULFILL ORDER), a dialog window pops up and you can specify the date the order was fulfilled, which may of course be a date in the past. I assume that this date is written to the DATEFULFILLED attribute in the ORDER entity. Problem is that no matter what date is entered as the fulfillment date, the DATEFULFILLED reverts to the current date. We need to be able to specify and retrieve the actual date the order was fulfilled, not the date CRM was updated. Grateful for any insights! Henricus ...

Graph where source data has different dates
I'm trying to do a comparision line graph with two series of data. The are both tracked by date with the same range (from April 2008 to April 2009). However one series has data monthly and the other has data weekly. When I try to combine on a graph the dates just don't line up. Is it possible to have these line up correctly. I've tried formatting the axis to days instead of months but it appears to only line up data points. So for the data reported monthly, it is graphed across the first 12 days. Hopefully that makes sense. If not, just let me know & I'll try t...

unable to set the numberformat of the range class
I was graciously given some code to format some cells for different currencies (yen, euro, dollar) based upon selecting the currency in one cell (k5) in my requisition. I got it to work, ready to launch the new form for use by my engineers, so I add protection. Now it seems I cannot protect those cells I want to format, in order for the code to work. Instead I get the error in the subject line of this post. Anyone know the fix? Below is the code, and btw, "worksheet" and "calculate" are the selections made at the top of the code window. Troy Private Sub Worksheet_Calcul...

COUNTIF: drag to change criteria, NOT range
Hello, I'm hoping that there's someone out there that can help with this problem. I'm trying to count values in one column so that I have an amount of each for a distribution. For example: 8 8 8 11 11 11 12 12 12 I'm using COUNTIF because it's the only one that seems to work, but whenever I drag to fill the series Excel doesn't fill in a linear fashion. Instead it repeats the formulas I've already entered. Here's an example of what I enter into the cells: =COUNTIF($A$2:$A$36,8) =COUNTIF($A$2:$A$36,9) =COUNTIF($A$2:$A$36,10) ....and so on. When I highlight...

Adding Time Format
I am adding time for a day and would like the total to be returned in a different format. If I needed the time between 8:30am and 12:00pm added up it displays 3:30 as my total. How do I have it display 3.5 as the total? Currently the total cell is formatted [hh]:mm. Thanks for the help. you need to do something like this A1: 8:30 AM A2: 12:00 PM A3: =(A2-A1)*24 and then format A3 as a number. "jtinne" wrote: > I am adding time for a day and would like the total to be returned in a > different format. If I needed the time between 8:30am and 12:00pm added up > i...

Access Run-time 07-26-07
Hello all, I have Access 2003, but I want to be able to make systems for people who don't have Access. I have the Access2000 run-time programme. Can I make systems in Access 2003 (saving in 2000 version) which will run on someone else's PC who only has the Access 2000 run-time version? Would it only work with the mdb file, as I can't make Access 2000 mde files with 2003? Would they still be locked out of the code with their run-time versions? Thanks, Franc. See below. "Franc Sutherland" <FrancSutherland@discussions.microsoft.com> wrote in message news:0...

GrandTotal based on fields in two separate subforms...
I have an AddOrdersForm, an OrderDetailsSubform and an ItemDetailsSubform. The 3 are linked by the OrderID. In the OrderDetailsSubform footer I have txtOrderDetailsTotal with =Nz(Sum([Extended Price]),0) as the control source. In the ItemDetailsSubform footer I have txtItemDetailsTotal with =Nz(Sum([ExtendedToppingPrice]),0) as the control source. On AddOrderForm I have txtTotalPrice. I am trying to add txtOrderDetailsTotal and txtItemDetailsTotal in txtTotalPrice but I keep getting ERROR#. I have tried the following in the txtTotalPrice control source: =[txtOrderDetailsTota...

Configuring Exchange 5.5 to use a RBL (Real-time Spam Black List)
Is it possible to use a RBL with Exchange 5.5? What's the simplest way? Don't want to manually enter every email address to block. Thanks Not directly. But majority of antivirus softwares for Exchange support RBL, for example Symantec Mail Security so you could do RBL's in antivirus software -- Regards, Dejan Foro dejan.foro@exchangemaster.net www.exchangemaster.net Join Exchange User Group Europe for free, post questions about Exchange, find useful Exchange links in our link library, chat with other members...See you at www.eugeurope.org/join "Cal_Cougar" ...

Date Format #9
Hi I copy a SQL Query output and place it into Excel. The time column shows dates as 4/7/2006 4:02:10 AM, 2/28/2006 10:37:39 AM, 3/1/2006 1:56:40 PM. IS there a way to get rid of the hr, min and secs ? I used the format cells function on eg 4/7/2006 4:02:10 AM, 2/28/2006 10:37:39 AM, 3/1/2006 1:56:40 PM. and the columns show 4/7/2006, 2/28/2006, 3/1/2006. However, when you select the each cell, it still actually retains the values as 4/7/2006 4:02:10 AM, 2/28/2006 10:37:39 AM, 3/1/2006 1:56:40 PM I need the dates only because I am plotting a graph. With the hr, min and sec fun...

Send Date to a Memo Field
I have a subform with the following fields: Status NoteField I would like the double click event for Status to do the following: Change the status to "Ordered" Change the Notefield to Notefield + The Current Date I have tried: Private Sub Status_DblClick(Cancel As Integer) Status.Text = "ORDERED" NoteField.SetFocus NoteField.Value = NoteField.Text & Date End Sub I have also tried Notefield.Text in place of NoteField.Value I can copy text but I cannot get the date into the NoteField field. Thanks Your code does fail, as stated. I did a little modif...

Error in in validating some dates
Hi all, i've installed SQL server 2000 english version and the CRM server in my native language, italian. Italian date formatting is different from the US model, i.e. we use 24/12/2004 instead of 12/24/2004. So, in some case, in particular for due dates, the validating of the suggested date format sends me an error and doesn't insert the record. I've set italian language on mail SQL server properties, but i got no benefit. Any idea about this behavior? Thanks in advance. Domenico. Hi Domenico, See if this helps at all: Go to Home > Settings > System Customization. ...

How do I format all worksheets in a workbook at one time ?
I want to create a workbook that has all worksheets formatted in the same manner. Is there anyway to do so without formatting each worksheet separately ? yes, Right click on any sheet tab, Select "Select All Sheets" from the pop-up menu. Have fun formatting the active sheet. When done take a look at any sheet and the formatting is done on ALL the selected sheets. Dan Knight "EBrock" wrote: > I want to create a workbook that has all worksheets formatted in the same > manner. Is there anyway to do so without formatting each worksheet > separately ? Dan, l...

Large Quantity of Data, Graphed in Time Intervals
I have a very large set of data (over 20,000 points) listed in minute intervals. I'd like to be able to create charts using different time intervals such as 5 minutes, 15 minutes etc. by using a pull down menu to select the interval and have the graph make itself. Is this possible? You can use a pivot table to group times by such intervals. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Kirsten" <Kirsten@discussions.microsoft.com> wrote in message news:811D6E3E-C457-4D36-BA47-1C835D6D670D@microsoft.com....

Right-Click menu and sum of ranges
Hi everyone, I know that If I select a range in a spreasheet (continuous, or b Ctrl+Clicking a few cells) I can see the sum of the values in the rang on the Status bar, but would it be possible to have this info somewher else? This is basically the only reason I have my Status bar visible, and a I like my Excel as unclutered as possible, I would like to get rid o it :) Ideally, I would love to be able to view this on the Right-Click menu but I dont think it is possible without somebody writing an add-in Does one like this exist? Thanks for your help, Mathia --------------------------------...

anyAttribute in base classes
Does any know how to tell xsd.exe (or Xmlserialize or Xmldeserialize) not to read/write the "xsi:*" type attributes for complex classes that inherit from other complex classes since after the first Serialization and subsequent Deserialization in which a base class has the anyAttribute element the Object Model will have an attribute created for an element that is for the xsi:* complex type: thereafter, the XML instance document will not parse since there are two attributes with xsi:*. For example, if the XML schema has a complex type called "A" in which there is the anyAttri...

Complicated Calculations on forms
Dear friends, need your valuable help again. I am working on a Medical Fund database. I have a table where all benefits info is kept, e.g. medicines, up to 300 Euros / year. Over 300 and up to 500, 50%, >500 – no money is refunded. Tables also for Employees, for Invoices etc. I have created a form with subform – Invoices and benefits (one invoice with many employees / benefits). On the many form I have the BenefitID (e.g. medicines), the employee no (is a unique value), the amount (as shown on the invoice) and the EligibleAmount (should be automatically calculated base...

How can I check the expiry date on my office trial version?
I recently activated the OEM trial version of MS Office on a PC that I was using for other applications before. I seem to remember that the expiry date displyed at activation was shorter than I expected and wanted to check this. So far the Office applications have not prompted me with warnings about the expiry. How can I display this information? Thanks, Peter Whenever you start you get nagging alerts informing you that you have xx days left or xx times left to use this application. You can also check whether or not it is trial by going to: Windows Logo/Word Options/Res...