Date / Time recordings on spreadsheet.

jo54321 · Jun 11, 2011

Hello all,

I am creating a bidding sheet for overtime.
The principles are that each staff member has there own row protectected by their own password, they open their row and bid for shifts by entering X in the relevant cell.
I am looking for a way of automatically date / time recording when bids are made by individuals. (automatically because if left to the individuals, it's open to abuse)
I have tried if functions and date functions (combined) but these continually update when the sheet is accessed and thus not giving an accurate recording if i need to use bids on a first come first served basis.

Is there anyway i can stop the date updating in the individual cells once entered?

Grateful for any suggestions.

An example of my sheet:

 <table class="MsoNormalTable" style="width:343.0pt;margin-left:4.65pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" border="0" cellpadding="0" cellspacing="0" width="457"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:11.3pt"> <td style="width:15.9pt;border:solid windowtext 1.0pt;mso-border-alt: solid windowtext .5pt;background:#BFBFBF;mso-background-themecolor:background1; mso-background-themeshade:191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" width="21">

[FONT=&quot]
[/FONT]

</td> <td style="width:40.95pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#BFBFBF;mso-background-themecolor:background1; mso-background-themeshade:191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" nowrap="nowrap" width="55">

[FONT=&quot]A[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#BFBFBF;mso-background-themecolor:background1; mso-background-themeshade:191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" nowrap="nowrap" width="45">

[FONT=&quot]B[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#BFBFBF;mso-background-themecolor:background1; mso-background-themeshade:191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" nowrap="nowrap" width="45">

[FONT=&quot]C[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#BFBFBF;mso-background-themecolor:background1; mso-background-themeshade:191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" nowrap="nowrap" width="45">

[FONT=&quot]D[/FONT]

</td> <td style="width:43.0pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#BFBFBF;mso-background-themecolor:background1; mso-background-themeshade:191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" nowrap="nowrap" width="57">

[FONT=&quot]E[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#BFBFBF;mso-background-themecolor:background1; mso-background-themeshade:191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" nowrap="nowrap" width="45">

[FONT=&quot]F[/FONT]

</td> <td style="width:36.05pt;border:solid windowtext 1.0pt;border-left: none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" width="48">

[FONT=&quot]G[/FONT]

</td> <td style="width:36.05pt;border:solid windowtext 1.0pt;border-left: none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" width="48">

[FONT=&quot]H[/FONT]

</td> <td style="width:36.05pt;border:solid windowtext 1.0pt;border-left: none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:11.3pt" width="48">

[FONT=&quot]I[/FONT]

</td> </tr> <tr style="mso-yfti-irow:1;height:11.5pt"> <td style="width:15.9pt;border:solid windowtext 1.0pt;border-top: none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:11.5pt" width="21">

[FONT=&quot]1[/FONT]

</td> <td style="width:40.95pt;border:none;mso-border-left-alt: solid windowtext .5pt;background:#FABF8F;mso-background-themecolor:accent6; mso-background-themetint:153;padding:0cm 5.4pt 0cm 5.4pt;height:11.5pt" nowrap="nowrap" width="55">

[FONT=&quot]Date[/FONT]

</td> <td colspan="4" style="width:144.25pt;border-top:none; border-left:solid windowtext 1.0pt;border-bottom:none;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#FFFFCC;padding:0cm 5.4pt 0cm 5.4pt;height:11.5pt" nowrap="nowrap" width="192">

[FONT=&quot]01/06/11[/FONT]

</td> <td colspan="4" style="width:141.9pt;border:none;border-right: solid windowtext 1.0pt;mso-border-right-alt:solid windowtext .5pt;background: #FFFFCC;padding:0cm 5.4pt 0cm 5.4pt;height:11.5pt" nowrap="nowrap" width="189">

[FONT=&quot]02/06/11[/FONT]

</td> </tr> <tr style="mso-yfti-irow:2;height:18.75pt"> <td style="width:15.9pt;border:solid windowtext 1.0pt;border-top: none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" width="21">

[FONT=&quot]2[/FONT]

</td> <td style="width:40.95pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-top-alt: 1.0pt;mso-border-left-alt:.5pt;mso-border-bottom-alt:.5pt;mso-border-right-alt: 1.0pt;mso-border-color-alt:windowtext;mso-border-style-alt:solid;background: #FABF8F;mso-background-themecolor:accent6;mso-background-themetint:153; padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="55">

[FONT=&quot]Shift[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-top-alt: 1.0pt;mso-border-left-alt:.5pt;mso-border-bottom-alt:.5pt;mso-border-right-alt: 1.0pt;mso-border-color-alt:windowtext;mso-border-style-alt:solid;background: #FABF8F;mso-background-themecolor:accent6;mso-background-themetint:153; padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="45">

[FONT=&quot]Am[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: #FABF8F;mso-background-themecolor:accent6;mso-background-themetint:153; padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="45">

[FONT=&quot]Pm[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: #FABF8F;mso-background-themecolor:accent6;mso-background-themetint:153; padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="45">

[FONT=&quot]Eve[/FONT]

</td> <td style="width:43.0pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: #FABF8F;mso-background-themecolor:accent6;mso-background-themetint:153; padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="57">

[FONT=&quot]Date[/FONT]

[FONT=&quot]Entered[/FONT]

</td> <td style="width:33.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext 1.0pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: #FABF8F;mso-background-themecolor:accent6;mso-background-themetint:153; padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="45">

[FONT=&quot]Am[/FONT]

</td> <td style="width:36.05pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#FABF8F;mso-background-themecolor:accent6; mso-background-themetint:153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">

[FONT=&quot]Pm[/FONT]

</td> <td style="width:36.05pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#FABF8F;mso-background-themecolor:accent6; mso-background-themetint:153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">

[FONT=&quot]Eve[/FONT]

</td> <td style="width:36.05pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;background:#FABF8F;mso-background-themecolor:accent6; mso-background-themetint:153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">

[FONT=&quot]Date[/FONT]

[FONT=&quot]Entered[/FONT]

</td> </tr> <tr style="mso-yfti-irow:3;height:18.75pt"> <td style="width:15.9pt;border:solid windowtext 1.0pt;border-top: none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" width="21">

[FONT=&quot]3[/FONT]

</td> <td style="width:40.95pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:#8DB3E2;mso-background-themecolor:text2;mso-background-themetint: 102;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="55">

[FONT=&quot]Smith[/FONT]

</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:43.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#D99594;mso-background-themecolor:accent2;mso-background-themetint: 153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="57">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" align="center" nowrap="nowrap" width="45">X
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" align="center" nowrap="nowrap" width="48">X
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="48">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#D99594;mso-background-themecolor:accent2;mso-background-themetint: 153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">02/06/11
</td> </tr> <tr style="mso-yfti-irow:4;height:18.75pt"> <td style="width:15.9pt;border:solid windowtext 1.0pt;border-top: none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" width="21">

[FONT=&quot]4[/FONT]

</td> <td style="width:40.95pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:#8DB3E2;mso-background-themecolor:text2;mso-background-themetint: 102;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="55">

[FONT=&quot]Bloggs[/FONT]

</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" align="center" nowrap="nowrap" width="45">X
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" align="center" nowrap="nowrap" width="45">X
</td> <td style="width:43.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#D99594;mso-background-themecolor:accent2;mso-background-themetint: 153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="57">01/06/11
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" align="center" nowrap="nowrap" width="45">X</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="48">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="48">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#D99594;mso-background-themecolor:accent2;mso-background-themetint: 153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">01/06/11</td> </tr> <tr style="mso-yfti-irow:5;height:18.75pt"> <td style="width:15.9pt;border:solid windowtext 1.0pt;border-top: none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" width="21">

[FONT=&quot]5[/FONT]

</td> <td style="width:40.95pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:#8DB3E2;mso-background-themecolor:text2;mso-background-themetint: 102;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="55">

[FONT=&quot]poodle[/FONT]

</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:43.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#D99594;mso-background-themecolor:accent2;mso-background-themetint: 153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="57">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="48">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext 1.0pt; background:white;mso-background-themecolor:background1;padding:0cm 5.4pt 0cm 5.4pt; height:18.75pt" nowrap="nowrap" width="48">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#D99594;mso-background-themecolor:accent2;mso-background-themetint: 153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">
</td> </tr> <tr style="mso-yfti-irow:6;mso-yfti-lastrow:yes;height:18.75pt"> <td style="width:15.9pt;border:solid windowtext 1.0pt;border-top: none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; background:#BFBFBF;mso-background-themecolor:background1;mso-background-themeshade: 191;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" width="21">

[FONT=&quot]6[/FONT]

</td> <td style="width:40.95pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:#8DB3E2;mso-background-themecolor: text2;mso-background-themetint:102;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="55">

[FONT=&quot]Jumper[/FONT]

</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:white;mso-background-themecolor: background1;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:white;mso-background-themecolor: background1;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:white;mso-background-themecolor: background1;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" align="center" nowrap="nowrap" width="45">X
</td> <td style="width:43.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:#D99594;mso-background-themecolor: accent2;mso-background-themetint:153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="57">31/05/11
</td> <td style="width:33.75pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:white;mso-background-themecolor: background1;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="45">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:white;mso-background-themecolor: background1;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" align="center" nowrap="nowrap" width="48">X
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:white;mso-background-themecolor: background1;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">
</td> <td style="width:36.05pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;background:#D99594;mso-background-themecolor: accent2;mso-background-themetint:153;padding:0cm 5.4pt 0cm 5.4pt;height:18.75pt" nowrap="nowrap" width="48">01/06/11</td> </tr> </tbody></table>

Akashwani · Jun 11, 2011

Hi,

I don't write or understand VBA so I am unable to help you further with code, sorry. Based on this sample data and code someone may give you a slicker code to achieve your objective.

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Date

01/06/2011

02/06/2011

2

Shift

Am

Pm

Eve

Date Entered

Am

Pm

Eve

Date Entered

3

Smith

X

*

11/06/2011 12:58

*

X

*

11/06/2011 12:57

4

Bloggs

*

X

*

11/06/2011 12:58

*

X

11/06/2011 12:57

5

Poodle

*

X

11/06/2011 12:58

*

X

*

11/06/2011 12:57

6

Jumper

*

X

*

11/06/2011 12:57

Sheet5

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 4 Then Exit Sub
    If Target.Column < 2 Then Exit Sub
    If Target.Column < 5 Then
        
        Application.EnableEvents = False
        Cells(Target.Row, 5).Value = Date + Time
        Application.EnableEvents = True
       
    End If
    
    If Target.Row < 4 Then Exit Sub
    If Target.Column < 6 Then Exit Sub
    If Target.Column > 5 Then
        
        Application.EnableEvents = False
        Cells(Target.Row, 9).Value = Date + Time
        Application.EnableEvents = True
    
    End If
End Sub

You may want to provide the range of your data so that a suitable code can be provided. Take a look here for some example codes....

http://dmcritchie.mvps.org/excel/event.htm

Good luck.

Ak

jasonb75 · Jun 11, 2011

In excel options > Formulas, enable iterative calculation, set maximum iterations to 1

in E4 =IF(COUNTA(B4:D4),IF(E4<>"",E4,NOW()),"")

Fill down and copy to other columns as needed.

edit:-

Forgot to add, use a custom format on the cells such as dd:mm:yy hh:mm

Akashwani · Jun 11, 2011

Hi Jason,

I maybe wrong here, but with your formula wont every date stamp change whenever any cell changes regardless of the row?

Ak

jasonb75 · Jun 11, 2011

No, the iterative formula locks the timestamp in place, (consider it as an on/off switch for the formula), it is changed only by the relative cluster of 3 cells.

=IF(COUNTA(B4:D4),IF(E4<>"",E4,NOW()),"")

Note the red section only comes in if there is a count in B4:D4, if all are empty then the formula returns ""

When there is a count and the red part is triggered, if E4 is anything other than "" then it returns it's own value, if it is blank then it returns the value of NOW(), once this happens it's no longer blank, which means it now returns it's own value.

Akashwani · Jun 11, 2011

Will changing the Iterations to 1 in Options have any effect on other formulas?
Mine is currently set to 100.

Ak

jasonb75 · Jun 11, 2011

If you had to tick the box then no, if it was already ticked then possibly, that said, setting it to 1 is not critical for this formula, it would still function correctly if left at 100.

If you're trying this it may be better to test with a different cell format such as hh:mm:ss.000 so that the timestamps are more accurate.

jo54321 · Jun 11, 2011

Thanx both
will try this

Date / Time recordings on spreadsheet.

jo54321

New Member

Excel Facts

Akashwani

Well-known Member

jasonb75

Well-known Member

Akashwani

Well-known Member

jasonb75

Well-known Member

Akashwani

Well-known Member

jasonb75

Well-known Member

jo54321

New Member

Similar threads

Forum statistics

Share this page

Date / Time recordings on spreadsheet.

New Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock