IF Statement and Time

[Volk359]

Greetings All,

I'd like to track sales not only on particular days but particular times too, specifically in 1/2 hour increments. My spreadsheet is as follows:

B C D
Day Time Sales

I'm able to get the formula to account for total sales on a day but how can I get it to recognize time increments? To start I'm trying to get it to recognize time before 9:00am:

=SUM(IF((B:B="Monday")*(C:C<"9:00:00 AM"),D))

which returns a zero and also tried Excel's decimal time:

=SUM(IF((B:B="Monday")*(C:C<0.375),D))

which also returns a zero. I'm at a loss here in addition to trying to figure out how to get it to calc ">8:31 AND <9:00".

Can someone point me in the right direction?

Thanks,

Keith

 

[SkipVought]

Hi,

First, use REAL DATES for column B

I use named ranges...
[tt]
=SUMPRODUCT((Dat=DATEVALUE("3/19/2008"))*(Tim<TIMEVALUE("9:00"))*(Sale))
[/tt]
would be alot simpler if you use cells for the DAT and TIM criteria...
[tt]
=SUMPRODUCT((Dat=E1)*(Tim<F1)*(Sale))
[/tt]
where E1 contains the Dat criteria and F1 contains the Tim criteria.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[AnotherHiggins]

Several problems:

1) "9:00:00 AM" is a text string, not a time. Saying <"9:00:00 AM" is like saying <Bob, it just doesn't make sense to Excel [red]*[/red]

2) Same problem with "Monday". That's a text string, not a date [red]*[/red]

3) You are trying to use an array formula. These are special formulas that loop through ranges and check each cell. Array Formulas must be entered using [Ctrl]+[Shift]+[Enter] instead of just [Enter]

4) You can't use entire column range references in your array formula, you'll have to use something like "B2:65536" instead

5) You don't have to worry about number 3 if you just use SumProduct instead of "Sum(If(". SumProduct is, by default, an array formula, so you can just enter it with plain ol' [Enter]. But you still can't use a reference for an entire column

[red]*[/red] Please read faq68-5827 for more information about how Excel deals with dates and times.

- - -

What is in column A? I'm hoping that it is a REAL date/time field. If not, do you have any column that contains the date & time?

As for your attempt to groups things by 30 minute increments, I'd add a column with this formula:
[tab]=FLOOR(A2-INT(A2),"0:30:00")

Also have a look at Pivot Tables. Once you have the data stored correctly, it should only take you a few seconds to create a report that shows you what you are trying to see.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Volk359]

Thanks for the replies and suggestions. Sorry, I should have been a bit more explict; we'd like to see how sales on, say, Mondays between 8:30 and 9:00 in the morning compare with Thursday between 2:00 and 2:30 in the afternoon.

John, I understand how Excel deals with time and dates that's why I figured the text format didn't work and figured the decimal format should. I thought, anyway. Also, your suggestion of using =FLOOR(A2-INT(A2),"0:30:00") sounds like a good idea but returned #NUM!.

Skip, your suggestion of using date value would work for individual days but not for weekdays. Column A has the real date which is converted to a weekday in column B. I've also converted the columns to named ranges however

=SUMIF(Day,"Sunday",Sales) works but gives me sales for all of that day.

=SUM(IF((Day="Monday")*(Time<TIMEVALUE("9:00")),Sales)) just returns a zero.

=SUMPRODUCT((Day="Sunday")*(Time<TIMEVALUE("9:00"))*(Sales)) returns #VALUE!

I initially thought about a pivot table but thought formulas might be easier.

 

[Volk359]

BTW, a pivot table works real well if I can only get the 1/2 hour increment thing.

 

[SkipVought]

USE REAL DATES!!! regardless if you want to specify a day of the week.

You limit yourself to ONE WEEKS WORTH OF DATA, a big mistake!

From a REAL DATE, you can derive the ddd or dddd day format using the TEXT function.

1/hr incriment thing:
Post starting time. Incriment by [prev ref] + 30/60/24

A formula like this used a day of week criteria on a REAL DATE
[tt]
=SUMPRODUCT((WEEKDAY(dte)=4)*(tim>=F1)*(tim<G1)*(sales))
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Volk359]

Ah, that's how you get the day in there. Sorry Skip, seems I'm still missing something here. I assume that cols F & G should be my incremented time? e.g.:

F1 = 8:00
F2 = +F1+30/60/24
F3 = +F2+30/60/24

and

G1 = 8:30
G2 = +G1+30/60/24
G3 = +G2+30/60/24

Formula:

=SUMPRODUCT((WEEKDAY(dte)=4)*(tim>=F2)*(tim<G2)*(sales)) returns #N/A

 

[SkipVought]

[tt]
F2 = 8:00
and

G2 = 8:30

Formula:

=SUMPRODUCT((WEEKDAY(dte)=4)*(tim>=F2)*(tim<G2)*(sales))
[/tt]
faq68-5827

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Volk359]

But, all that did was change the format of F2 & G2, correct? The formula still returns #N/A.

 

[SkipVought]

are your column ranges
dte (real dates),
tim (real times) and
sales?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Volk359]

Yes, real dates and times. Here's a link to a sample file if you would be so kind to take a look at.

http://www.intlacademy.us/sales_test.xls

Thanks

 

[SkipVought]

ONLY include rows tha contain data, when naming your ranges.

The #N/A immediately returns 4.25.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Volk359]

And so it does. Thanks much for your help and patience!