Calc Time

H

healey21

Well-known Member
Joined
Dec 22, 2009
Messages
900
I have been asked to look at creating a spreadsheet when data is added regularly with date and time entries and count Ledger.

What is requried is to create a total every 15 minutes for the entries that are added to the ledger. so total the count ledger from the time entry every 15 minutes.

Can anyone guide me as I am unsure of time interval totals! A smaple of the data is shown below:

Excel Workbook
ABCD
1StaffTime CompletedCount LedgerTotal Every 15 Minutes
2John19/08/2011 15:301
3Bill19/08/2011 15:311
4Sarah19/08/2011 15:321
5Jane19/08/2011 15:331
6Anne19/08/2011 15:352
7Allan19/08/2011 15:371
8David19/08/2011 15:401
9John19/08/2011 15:421
10Bill19/08/2011 15:431
11Sarah19/08/2011 15:441
12Jane19/08/2011 15:481
13Anne19/08/2011 15:501
14Allan19/08/2011 15:521
15David19/08/2011 15:531
16John19/08/2011 15:551
17Bill19/08/2011 15:561
18Sarah19/08/2011 16:031
19Jane19/08/2011 16:041
20Anne19/08/2011 16:082
21Allan19/08/2011 16:112
22David19/08/2011 16:161
23John19/08/2011 16:171
24Bill19/08/2011 16:191
25Sarah19/08/2011 16:201
26Jane19/08/2011 16:221
27Anne19/08/2011 16:231
28Allan19/08/2011 16:252
Count Legder
Click to expand...
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Are you looking at just a total for every 15 minute so having a cell with =Now() in it and then looking at the Column with Date and Time work out the Now Function but everything 15 minutes before it and count the column of Ledger entries.

I think if so then something like SUMPRODUCT would work.
 
Upvote 0
Thanks Trevor G

I have tried this, but it doesn't seem to give me a result?

Count Legder

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 42px"><COL style="WIDTH: 111px"><COL style="WIDTH: 171px"><COL style="WIDTH: 148px"><COL style="WIDTH: 111px"><COL style="WIDTH: 122px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Staff</TD><TD style="TEXT-ALIGN: center">Time Completed</TD><TD style="TEXT-ALIGN: center">Count Ledger</TD><TD style="TEXT-ALIGN: center">Total Every 15 Minutes</TD><TD style="TEXT-ALIGN: left">Now</TD><TD style="TEXT-ALIGN: left">15 minutes before</TD><TD style="TEXT-ALIGN: center">Total Count</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>John</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:08</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="TEXT-ALIGN: right">26/08/2011 10:23</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:08</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:09</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:10</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Jane</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:11</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Anne</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:12</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Allan</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:13</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>David</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:14</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>John</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:15</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:16</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:17</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Jane</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:18</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=NOW()</TD></TR><TR><TD>F2</TD><TD>=E2-TIME(0,15,0)</TD></TR><TR><TD>G2</TD><TD>=SUMPRODUCT(B:B>E2)*(B:B<F2)*(C:C)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
 
Last edited:
Upvote 0
Change the forumla like this

Count Legder

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 42px"><COL style="WIDTH: 111px"><COL style="WIDTH: 89px"><COL style="WIDTH: 148px"><COL style="WIDTH: 111px"><COL style="WIDTH: 122px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Staff</TD><TD style="TEXT-ALIGN: center">Time Completed</TD><TD style="TEXT-ALIGN: center">Count Ledger</TD><TD style="TEXT-ALIGN: center">Total Every 15 Minutes</TD><TD style="TEXT-ALIGN: left">Now</TD><TD style="TEXT-ALIGN: left">15 minutes before</TD><TD style="TEXT-ALIGN: center">Total Count</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>John</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:49</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="TEXT-ALIGN: right">26/08/2011 10:46</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:31</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:48</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:47</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Jane</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:46</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Anne</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:45</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Allan</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:44</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>David</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:43</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>John</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:42</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:41</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:40</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Jane</TD><TD style="TEXT-ALIGN: right">26/08/2011 10:39</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-FAMILY: Times New Roman"> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=NOW()</TD></TR><TR><TD>F2</TD><TD>=E2-TIME(0,15,0)</TD></TR><TR><TD>G2</TD><TD>=SUMPRODUCT(--(B2:B12>F2),--(B2:B12<E2),(C2:C12))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
How to make excel count based on time data?
Replies
3
Views
501
Fluff
Fluff
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
191
Madam_Xavier
M
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
364
dilshod_k
D
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
197
Tara_A_Data
T
P
  • Question Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
431
POLACOLOCO
P

Forum statistics

Threads
1,224,522
Messages
6,179,292
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top