How to make a Formula containing times.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sapphyre - 12 Jul 2007 19:12 GMT

How can I make a formula calculate how many occurances of a specific time
frame happened in Sheet 1, column A, and put it in Sheet 2, column A, B, C?

Example of the colums I'm speaking of:

Sheet 1: contains start time of projects during 3 different shifts
A
START TIME (Header)
07/01/07 03:00
07/01/07 05:50
07/01/07 07:00
07/01/07 11:00
07/01/07 15:10
07/01/07 18:37
07/01/07 23:01

Sheet 2:
A B
C
7a-3p Shift (Header) 3p-11p Shift (Header) 11p-7a Shift (Header)
2 2
3

7a-3p=07:00 a.m. - 14:59 p.m., 3p-11p=15:00 p.m. - 22:59 pm., and
11p-7a=23:00 - 6:59 a.m.,

Any help would be appreciated.
Thank you.

Reply to this Message

Sandy Mann - 12 Jul 2007 19:51 GMT

Try:
=SUMPRODUCT((Sheet1!B3:B9>=--("7:00"))*(Sheet1!B3:B9<--("15:00")))
=SUMPRODUCT((Sheet1!B3:B9>=--("15:00"))*(Sheet1!B3:B9<--("23:00")))
=SUMPRODUCT((Sheet1!B3:B9>=--("23:00"))*(Sheet1!B3:B9<--("24:00")))+SUMPRODUCT((Sheet1!B3:B9>=--("0:00"))*(Sheet1!B3:B9<--("7:00")))

Adjust the ranges to suit your needs.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> How can I make a formula calculate how many occurances of a specific time
> frame happened in Sheet 1, column A, and put it in Sheet 2, column A, B,
[quoted text clipped - 25 lines]
> Any help would be appreciated.
> Thank you.

Reply to this Message

Sapphyre - 12 Jul 2007 22:08 GMT

Ok, tried your formulas (just changed B range to A) for each column and it
didn't work. Not sure why. Could it be because in Sheet 1 column A I also
have dates?

> Try:
> =SUMPRODUCT((Sheet1!B3:B9>=--("7:00"))*(Sheet1!B3:B9<--("15:00")))
[quoted text clipped - 31 lines]
> > Any help would be appreciated.
> > Thank you.

Reply to this Message

Peo Sjoblom - 12 Jul 2007 22:16 GMT

Wrap the ranges in

MOD(range,1)=--"15:00"

etc

to get just times

Signature

Regards,

Peo Sjoblom

> Ok, tried your formulas (just changed B range to A) for each column and it
> didn't work. Not sure why. Could it be because in Sheet 1 column A I
[quoted text clipped - 39 lines]
>> > Any help would be appreciated.
>> > Thank you.

Reply to this Message

Sapphyre - 12 Jul 2007 22:30 GMT

Thanks for the replies. I think I might be over my head trying to get this
formula to work. I know just one wrong .,-() can mess up the whole thing and
not sure where exactly you wanted me to put MOD(range,1)=--"15:00" in the
formula. I will keep at it though :)....thanks again

> Wrap the ranges in
>
[quoted text clipped - 47 lines]
> >> > Any help would be appreciated.
> >> > Thank you.

Reply to this Message

Sandy Mann - 12 Jul 2007 22:45 GMT

I tried that Peo but I ran into rounding problems with MOD(range,1)=--"7:00"

With 7:00 in A1 and 07/01/07 07:00 in B1
=Mod(A1,1) -mod(B1,1) returns 6.79089E-10

=SUMPRODUCT((ROUND(MOD(Sheet1!A3:A9,1),6)>=--("7:00"))*(ROUND(MOD(Sheet1!A3:A9,1),6)<--("15:00")))

Returns the right answer, (rounding to 9 places does not), but I would not
be confident that it would in all circumstances.

It would be much better if the OP separated the dates and times in different
columns, which of course is what I had originally.

Signature

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Wrap the ranges in
>
[quoted text clipped - 48 lines]
>>> > Any help would be appreciated.
>>> > Thank you.

Reply to this Message

Sapphyre - 12 Jul 2007 23:18 GMT

Thank you Sandy, I see now where you might have misunderstood my date/time.
They are both in column A, not date A and time B, that was my fault in my
original post, I should have clarified better.

Anyway, I think you are right in breaking up the date and time into 2
different columns. I have been trying to get data that my boss wants from a
spread sheet that was already in place when I started working there. Would
have been much easier to just start a whole new one from scratch, but it's
getting there.

Thank you for all your help.

> I tried that Peo but I ran into rounding problems with MOD(range,1)=--"7:00"
>
[quoted text clipped - 61 lines]
> >>> > Any help would be appreciated.
> >>> > Thank you.

Reply to this Message

Sandy Mann - 13 Jul 2007 00:07 GMT

If it doesn't mess up the rest of the sheet you can insert a new column B,
highlight the Dates & times in columns A and then select Data > Text to
Columns > Delimited > Next > check Space > Finish, reformat the columns to
what you want then use the original formulas.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Thank you Sandy, I see now where you might have misunderstood my
> date/time.
[quoted text clipped - 82 lines]
>> >>> > Any help would be appreciated.
>> >>> > Thank you.

Reply to this Message