Inserting time in spreadsheet

[papabill]

I have a spreadsheet that I keep track of my blood sugar levels (I'm a diabetic), and it's set up like this:


Date Blood Level Time Blood Level Time Blood Level Time
Sun-Dec-09 242 7:30 AM 206 1:21 PM

Each time I take my blood sugar level, I must record the level and the time. The level fluctuates, but the time has to be entered manually. I have tried =now() as the time, but every time I open the spreadsheet, anything in the =now() changes to the current time. I need some way to automatically enter the ACTUAL time and keep it from changing.

Does this make sense? And, can anyone help me?

Thanks

[rollis13]

Assuming your first Blood Level (242) is in cell B2, in cell C2 (col. Time) copy this formula.

=IF(ISBLANK(B2),"",IF(C2="",NOW(),C2))

When you fill in B2 and C2 is empty the time will fill in and will not change unless you empty B2 again.

[papabill]

Assuming your first Blood Level (242) is in cell B2, in cell C2 (col. Time) copy this formula.
=IF(ISBLANK(B2),"",IF(C2="",NOW(),C2))
When you fill in B2 and C2 is empty the time will fill in and will not change unless you empty B2 again.

I guess I should have gone into more detail.

There are a possibility of 12 "tests" a day, two columns, one for level, the other for time (each one for a 2 hour span), for every day in the month (this month has 31 days) The spreadsheet covers from B4..Y34. The "level" slots are B,D,F,H,J,L,N,P,R,T,V,and X, and the "time" slots are C,E,G,I,K,M,O,Q,S,U,W, and Y.

Each level goes in one column, and the time goes in the adjacent column (to the right). I only test every 4 hours or so, so of course I don't enter data in every cell.

I'm probably making this infinately harder, but it would be great if I could get a workable solution.

Thanks in advance.

[rollis13]

From the cell C2 just copy-down as needed the formula in column C and then copy the entire column C to all the other Time columns.

[papabill]

Thank you so much!

[rollis13]

Glad being of some help :).

[Eiopqrtuwy]

I need some way to automatically enter the ACTUAL time and keep it from changing.

Does this make sense? And, can anyone help me?

Thanks

Excel has some keyboard shortcuts to do this:
Insert Date: Ctrl + ;
Insert Time: Ctrl + Shift + ;

Alternatively, here are two other slightly more tedious, (but more flexible) ways to turn dynamic equations into fixed values:
1) After you've imputed your equations into want "Copy" then "Paste as Values" over the same cell(s). Can be for a single cell or a range of cells.
2) While imputing an equation highlight the part you no longer want to be volatile and press "F9" (e.g. highlight the "=NOW()" in your formula bar and press "F9", then "Enter" to keep the changes.)

[papabill]

Thanks, but the solution that rollis13 gave me works beautifully. But I do appreciate the extra info. Who knows, I might be able to use it somewhere else.

[papabill]

Well, ain't it my luck. The VERY first day of a new year, my blood level is perfect (103 at getting out of bed), and my new sheet will NOT work, and I keep getting a "circular reference" warning.

I hope the REST of the year isn't going to be this bad.

Thanks

[rollis13]

Maybe if you copy the entire sheet to the new sheet and then clear all the manual inputted data it will be ok.

[papabill]

Here is the spreadsheet (http://www.sq1bbs.com/book1.zip) for January 2013. Maybe you can spot my error.

Thanks

[rollis13]

You probably started with a new book. Go to the menu Tools / then Options / in the Calculation Tab / check the Iteration checkbox / and make Maximum iterations = 1 .

[papabill]

Yeppers, that's all it took.

Thanks

[papabill]

Well, I'm at it again, trying to get this spreadsheet to do what I need it to do. So far, so good, thanks to all the help from everyone here.

Now, I need to protect ONLY certain parts of the sheet. All the places where I need to input figures should be available, but everything else needs to be protected against change, because sometimes when I'm in a hurry, I don't put the cursor in exactly the right spot, and if I don't notice it right away, I have a problem.

Here is what I want:



The date and time columns need to be protected against change, but the level columns open for entry.

Date | Time |Level| Time |Level| Time |Level| Time |Level|
Tue, Oct 01, 2013 | 7:37AM | 110 | 12:25PM | 100 | 5:46PM | 97 | 10:07PM | 113 |
Wed, Oct 02, 2013 | 6:32AM | 108 | 1:23PM | 96 | 5:26PM | 96 | 12:04AM | 96 |
Thu, Oct 03, 2013 | 8:12AM | 142 | 2:33PM | 101 | 10:28PM | 73 | 1:12AM | 107 |
Fri, Oct 04, 2013 | 7:09AM | 87 | 12:33PM | 121 | 3:54PM | 97 | 11:31PM | 122 |



I have wrestled with the Review -> Protect Worksheet for a couple of days, and cannot get it right. And, oh, yeah, I'm using Microsoft Excel 2007

Thanks for your help.

[Rick Rothstein]

Now, I need to protect ONLY certain parts of the sheet. All the places where I need to input figures should be available, but everything else needs to be protected against change, because sometimes when I'm in a hurry, I don't put the cursor in exactly the right spot, and if I don't notice it right away, I have a problem.

Here is what I want:



The date and time columns need to be protected against change, but the level columns open for entry.

Date | Time |Level| Time |Level| Time |Level| Time |Level|
Tue, Oct 01, 2013 | 7:37AM | 110 | 12:25PM | 100 | 5:46PM | 97 | 10:07PM | 113 |
Wed, Oct 02, 2013 | 6:32AM | 108 | 1:23PM | 96 | 5:26PM | 96 | 12:04AM | 96 |
Thu, Oct 03, 2013 | 8:12AM | 142 | 2:33PM | 101 | 10:28PM | 73 | 1:12AM | 107 |
Fri, Oct 04, 2013 | 7:09AM | 87 | 12:33PM | 121 | 3:54PM | 97 | 11:31PM | 122 |



Something modeled around this should work for you...



Sub ProtectAllExceptLevels()
Sheets("Sheet2").Unprotect
Cells.Locked = True
Intersect(Range("C:C,E:E,G:G,I:I").EntireColumn, Range("2:" & Rows.Count)).Locked = False
Sheets("Sheet2").Protect
End Sub

[papabill]

I'm sorry, I'm not a programmer, so this is totally unknown to me. I'm gonna need some actions to take in the spreadsheet itself.

Thanks

[rollis13]

You need to right click the TabName of the sheet to open it's VBA panel then copy the macro in the right blank space.
Have a try with this after changing the sheet's name in the macro (now it is Sheet1):
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim c As Long
Dim Cell As Range

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
End With

If ActiveSheet.Name = "Sheet1" Then 'change name of sheet as needed
'Resume to next line if any error occurs
On Error Resume Next
With ActiveSheet
.Unprotect 'unprotect entire sheet
'search for non blank cells and lock them and unlock blank cells
For Each Cell In ActiveSheet.UsedRange
r = Cell.Row
c = Cell.Column
If Cell.Value <> "" Then
If Cell.Locked = False Then Cell.Locked = True
End If
Next Cell
.Protect 'protect entire sheet
End With

With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.ScreenUpdating = True
End With

End If
End Sub

[Bjarnehansen]

try this sheet: