|
How to make a Formula containing times.
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. |
How to make a Formula containing times.
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? "Sandy Mann" wrote: 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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sapphyre" wrote in message ... 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. |
How to make a Formula containing times.
Wrap the ranges in
MOD(range,1)=--"15:00" etc to get just times -- Regards, Peo Sjoblom "Sapphyre" wrote in message ... 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? "Sandy Mann" wrote: 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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sapphyre" wrote in message ... 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. |
How to make a Formula containing times.
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 "Peo Sjoblom" wrote: Wrap the ranges in MOD(range,1)=--"15:00" etc to get just times -- Regards, Peo Sjoblom "Sapphyre" wrote in message ... 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? "Sandy Mann" wrote: 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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sapphyre" wrote in message ... 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. |
How to make a Formula containing times.
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. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Peo Sjoblom" wrote in message ... Wrap the ranges in MOD(range,1)=--"15:00" etc to get just times -- Regards, Peo Sjoblom "Sapphyre" wrote in message ... 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? "Sandy Mann" wrote: 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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sapphyre" wrote in message ... 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. |
How to make a Formula containing times.
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. "Sandy Mann" wrote: 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. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Peo Sjoblom" wrote in message ... Wrap the ranges in MOD(range,1)=--"15:00" etc to get just times -- Regards, Peo Sjoblom "Sapphyre" wrote in message ... 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? "Sandy Mann" wrote: 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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sapphyre" wrote in message ... 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. |
How to make a Formula containing times.
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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sapphyre" wrote in message ... 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. "Sandy Mann" wrote: 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. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Peo Sjoblom" wrote in message ... Wrap the ranges in MOD(range,1)=--"15:00" etc to get just times -- Regards, Peo Sjoblom "Sapphyre" wrote in message ... 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? "Sandy Mann" wrote: 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. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sapphyre" wrote in message ... 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. |
| All times are GMT +1. The time now is 03:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com