retriving a percentage from two dates

A

aphexcloud

New Member
Joined
Jul 20, 2011
Messages
10
Hi

i have another beginner issue...
i have two dates that i need to retrive a percentage from.
the optimal turn around time is 24 hours so if i recive a item on 26/7/11
100% would be a delivery it reaching its destination on 27/7/11
but i do have some days that are done on the same day which if possible would like it to show over 100% if delivered same day
oh it also has time in with the date but i want it to be ignored

ps i am using excel 2007 but can use 2003 or 2010

example:
Sheet1

<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: 94px"><COL style="WIDTH: 64px"><COL style="WIDTH: 89px"><COL style="WIDTH: 108px"><COL style="WIDTH: 113px"><COL style="WIDTH: 94px"><COL style="WIDTH: 147px"><COL style="WIDTH: 101px"></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><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Label</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Action</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Run Description</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Captured</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Label</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Action</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Scan Time</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Captured</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104484</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">8/06/11 16:32:43</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104484</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17:31:00</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">9/06/11 10:58:06</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104485</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">8/06/11 16:32:43</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104485</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17:31:00</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">9/06/11 11:06:10</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104486</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">8/06/11 16:32:43</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104486</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17:31:00</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">9/06/11 14:53:09</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104487</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Berwick</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">14/06/11 11:39:49</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104487</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:38:27</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">15/06/11 7:43:54</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104488</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Berwick</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">15/06/11 11:53:32</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104488</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:51:55</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">16/06/11 10:13:09</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104489</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">16/06/11 11:27:08</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104489</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:25:50</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">16/06/11 14:27:18</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104490</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">16/06/11 11:27:08</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104490</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:25:50</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">20/06/11 11:30:36</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104491</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">16/06/11 11:27:08</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104491</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:25:50</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17/06/11 9:58:09</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104493</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">16/06/11 11:27:08</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104493</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:25:50</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">20/06/11 10:22:10</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104495</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">17/06/11 11:31:21</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104495</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:29:58</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17/06/11 14:52:59</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104496</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Berwick</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">23/06/11 11:26:04</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104496</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:24:59</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">23/06/11 15:27:21</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104497</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Berwick</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">23/06/11 11:26:04</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104497</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:24:59</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">23/06/11 15:27:21</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1104498</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Berwick</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">23/06/11 11:26:04</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1104498</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:24:59</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">23/06/11 15:27:21</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1516723</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">2/06/11 16:47:37</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1516723</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17:46:00</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">3/06/11 14:43:24</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1516724</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">6/06/11 11:42:04</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:40:29</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1516725</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">6/06/11 11:42:03</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:40:29</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1516726</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">6/06/11 11:42:03</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:40:29</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1516727</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">6/06/11 11:42:03</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">12:40:29</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1516728</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">14/06/11 10:22:12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1516729</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">14/06/11 10:22:12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1516730</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">14/06/11 10:22:12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1516731</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">17/06/11 16:13:57</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1516731</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17:12:14</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">20/06/11 10:05:35</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">RBAML1516732</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Pickup</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">Dandenong North</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: MS Sans Serif; FONT-SIZE: 8pt">17/06/11 16:13:57</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">RBAML1516732</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">Delivery</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">17:12:14</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: MS Sans Serif; COLOR: #ffffff; FONT-SIZE: 8pt">20/06/11 10:05:35</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

thanks in advance
aphexcloud
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
oh not sure if i should have included this but the current excel file has had a macro used to lign the rows up proply


Rich (BB code): 
Option Explicit
Sub AlignLabelV2()
' hiker95, 07/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=565822
' The macro was modified from code by:
' Krishnakumar, 12/12/2010
' http://www.ozgrid.com/forum/showthread.php?t=148881
Dim ws As Worksheet
Dim LR As Long, a As Long
Dim Label As Range
Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1")
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.Range("A2:F" & LR).Sort Key1:=ws.Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
LR = ws.Range("G" & ws.Rows.Count).End(xlUp).Row
ws.Range("G2:L" & LR).Sort Key1:=ws.Range("G2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Set Label = ws.Range("A2:F" & LR)
a = 2
Do While Label.Cells(a, 1) <> ""
If Label.Cells(a, 1).Offset(, 6) <> "" Then
If Label.Cells(a, 1) < Label.Cells(a, 1).Offset(, 6) Then
Label.Cells(a, 1).Offset(, 6).Resize(, 6).Insert -4121
ElseIf Label.Cells(a, 1) > Label.Cells(a, 1).Offset(, 6) Then
Label.Cells(a, 1).Resize(, 6).Insert -4121
LR = LR + 1
Set Label = ws.Range("A2:F" & LR)
End If
End If
a = a + 1
Loop
Application.ScreenUpdating = 1
End Sub
 
Upvote 0
It's trivial calculating the transit time and you can ignore the exact time by using ROUNDDOWN() but the question is what you would like the % to be for late deliveries?

The easiest option would be =IF((ROUNDDOWN(H2,0)-ROUNDDOWN(D2,0)=0),">100%",1/(ROUNDDOWN(H2,0)-ROUNDDOWN(D2,0)))

That results in 50% for 2 days, 33% for 3 days etc.
 
Last edited:
Upvote 0
thank you all for your responses i have had a go to getting a figure and i can get a figure but it defiantly is not a percentage(or a unusable percentage)



Excel Workbook
ABCDEFGHIJ
1LabelActionScan DateRun DescriptionLabelActionScan DateRun DescriptionCaptured*
2RBAML1104484Pickup8/06/11 17:31:00Dandenong NorthRBAML1104484Delivery9/06/11 11:57:40Mentone9/06/11 10:58:0640702.72986
3RBAML1104485Pickup8/06/11 17:31:00Dandenong NorthRBAML1104485Delivery9/06/11 12:05:51Northcote9/06/11 11:06:10*
4RBAML1104486Pickup8/06/11 17:31:00Dandenong NorthRBAML1104486Delivery9/06/11 15:52:38Dandenong North9/06/11 14:53:09*
5RBAML1104487Pickup14/06/11 12:38:27BerwickRBAML1104487Delivery15/06/11 7:43:41Coburg North15/06/11 7:43:54*
6RBAML1104488Pickup15/06/11 12:51:55BerwickRBAML1104488Delivery16/06/11 11:12:41Bentleigh16/06/11 10:13:09*
7RBAML1104489Pickup16/06/11 12:25:50Dandenong NorthRBAML1104489Delivery16/06/11 15:26:52Glen Waverley16/06/11 14:27:18*
8RBAML1104490Pickup16/06/11 12:25:50Dandenong NorthRBAML1104490Delivery20/06/11 12:30:09Frankston20/06/11 11:30:36*
9RBAML1104491Pickup16/06/11 12:25:50Dandenong NorthRBAML1104491Delivery17/06/11 10:57:53Berwick17/06/11 9:58:09*
10RBAML1104493Pickup16/06/11 12:25:50Dandenong NorthRBAML1104493Delivery20/06/11 11:21:50Berwick20/06/11 10:22:10*
11RBAML1104495Pickup17/06/11 12:29:58Dandenong NorthRBAML1104495Delivery17/06/11 15:52:30Thomastown17/06/11 14:52:59*
12RBAML1104496Pickup23/06/11 12:24:59BerwickRBAML1104496Delivery23/06/11 16:26:54Notting Hill23/06/11 15:27:21*
13RBAML1104497Pickup23/06/11 12:24:59BerwickRBAML1104497Delivery23/06/11 16:26:54Notting Hill23/06/11 15:27:21*
14RBAML1104498Pickup23/06/11 12:24:59BerwickRBAML1104498Delivery23/06/11 16:26:54Notting Hill23/06/11 15:27:21*
15RBAML1516723Pickup2/06/11 17:46:00Dandenong NorthRBAML1516723Delivery3/06/11 15:42:50Heidelberg3/06/11 14:43:24*
16RBAML1516724Pickup6/06/11 12:40:29Dandenong North******
17RBAML1516725Pickup6/06/11 12:40:29Dandenong North******
18RBAML1516726Pickup6/06/11 12:40:29Dandenong North******
19RBAML1516727Pickup6/06/11 12:40:29Dandenong North******
20****RBAML1516728Delivery14/06/11 11:21:35Bayswater North14/06/11 10:22:12*
21****RBAML1516729Delivery14/06/11 11:21:35Bayswater North14/06/11 10:22:12*
22****RBAML1516730Delivery14/06/11 11:21:35Bayswater North14/06/11 10:22:12*
23RBAML1516731Pickup17/06/11 17:12:14Dandenong NorthRBAML1516731Delivery20/06/11 11:04:13Star Depot20/06/11 10:05:35*
24RBAML1516732Pickup17/06/11 17:12:14Dandenong NorthRBAML1516732Delivery20/06/11 11:04:13Star Depot20/06/11 10:05:35*
25****RBAML1601914Delivery7/06/11 10:49:07Notting Hill7/06/11 9:49:44*
26****RBAML1601978Delivery1/06/11 9:50:09Notting Hill1/06/11 8:50:24*
27****RBAML1601979Delivery1/06/11 9:50:09Notting Hill1/06/11 8:50:24*
28****RBAML1601980Delivery1/06/11 11:35:08Croydon1/06/11 10:35:23*
29****RBAML1601981Delivery1/06/11 11:35:08Croydon1/06/11 10:35:23*
30****RBAML1601984Delivery1/06/11 12:48:42Frankston1/06/11 11:51:03*
31****RBAML1601985Delivery1/06/11 12:48:42Frankston1/06/11 11:51:03*
32****RBAML1601986Delivery1/06/11 12:48:42Frankston1/06/11 11:51:03*
33****RBAML1601987Delivery1/06/11 10:21:47Clayton1/06/11 9:22:03*
34RBAML1601988Pickup1/06/11 12:24:11Dandenong NorthRBAML1601988Delivery1/06/11 15:34:40Berwick1/06/11 14:34:59*
35RBAML1601989Pickup1/06/11 12:24:11Dandenong NorthRBAML1601989Delivery1/06/11 15:34:40Berwick1/06/11 14:34:59*
36RBAML1601990Pickup1/06/11 12:24:11Dandenong North******
37RBAML1601991Pickup1/06/11 12:24:11Dandenong North******
38RBAML1601992Pickup1/06/11 12:24:11Dandenong NorthRBAML1601992Delivery2/06/11 12:45:15Frankston2/06/11 11:45:52*
39RBAML1601993Pickup1/06/11 12:24:11Dandenong North******
40RBAML1601994Pickup1/06/11 12:24:11Dandenong North******
41****RBAML1601995Delivery3/06/11 8:20:10Footscray3/06/11 8:20:42*
42****RBAML1601996Delivery3/06/11 8:20:10Footscray3/06/11 8:20:42*
43****RBAML1601997Delivery3/06/11 8:20:10Footscray3/06/11 8:20:42*
Sheet1



this is what i get some 4000 number which may be a percentage but i would like within 24 hrs to be 100% and any earlier would over 100% not fused by how much percent just need it to know what percent if possible

if it is easier a percentage next to each line and where there is no corresponding date for it to be 100%

thanks in advance



ps: off topic i know but is there any way to get training from mrexcell when i am in Australia as i am the most pc savvy person in our business at the moment and would like to be able to use vb code and excell.
lol
 
Upvote 0
Your original spreadsheet has changed. Originally the capture times were in columns D and H. Now they are in C and I. For your latest spreadsheet, copy this formula into J2

=IF((ROUNDDOWN(I2,0)-ROUNDDOWN(C2,0)=0),">100%",1/(ROUNDDOWN(I2,0)-ROUNDDOWN(C2,0)))

Then copy J2 down the full length of the spreadsheet.
 
Upvote 0
Sorry I missed the requirement for data lacking to return 100%. This should correct that:

=IF(OR(ISNUMBER(C2)=FALSE,ISNUMBER(I2)=FALSE),1,IF((ROUNDDOWN(I2,0)-ROUNDDOWN(C2,0)=0),">100%",1/(ROUNDDOWN(I2,0)-ROUNDDOWN(C2,0))))

Don't forget to format col J as percentage.

<TABLE style="WIDTH: 86pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=114 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 86pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 width=114 align=left>


</TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,521
Messages
6,179,275
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