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.