Need formula for time between two events

[k1ngr]

As a diabetic, I need to keep track of my blood sugar count and the time
elapsed between my last meal and when I test my blood sugar.

I have two Excel 2007 worksheets, BLOOD & MEAL in the same workbook. I
don't always test my blood sugar after every meal, so there is NOT a
one-to-one correspondence between entries in the two worksheets.

I don't keep both Meals & Blood Sugar in the same worksheet because I use
MEALS for keeping track of other things in addition to Blood Sugar.

What formula can I put into BLOOD, COL C to get the time elasped since my
last meal?

==============================
BLOOD
A B C
1 DATE_TIME BSC ELASPED_HOURS
2 01/02/08 06:00AM 099 8.00 <--------Formula to get this result?
3 01/02/08 09:00AM 225 1.00 "
4 01/02/08 06:00PM 155 1.00 "
5 01/02/08 07:00PM 162 2.00 "
6 01/03/08 06:00AM 091 8.50 "
7 01/03/08 10:30AM 222 1.75 "
8 01/03/08 12:15PM 125 1.25 "
9 01/03/08 07:30PM 133 2.15 "
==============================
MEALS
A B C
1 DATE_TIME
2 01/01/08 10:00PM
3 01/02/08 08:00AM
4 01/02/08 12:30PM
5 01/02/08 05:00PM
6 01/02/08 09:30PM
7 01/03/08 08:45AM
8 01/03/08 11:00AM
9 01/03/08 05:15PM
==============================
Thanks in advance,

Dick King

 

[T. Valko]

It looks like all you need to do is to subtract the blood date/time from the
last food date/time. However, when I tried this I got a couple of different
results compared to yours.

Your results.....My results
........8..................8.......
........1.................1......
........1.................5.5.....
........2.................2......
........8.5..............8.5....
.......1.75............1.75..
.......1.25............1.25..
.......2.15............2.25..

Also, you'd need to enter the date/time in a true Excel date/time format.

This is evaluated as a TEXT entry: 01/02/08 06:00AM

A true Excel date/time format would be: 01/02/08 6:00 AM

This is the formula I used:

=(A2-Meal!A2)*24

Then copied down

 

[Tyro]

Assuming your dates and times are in Excel date/time format in one cell then
this formula will
take the blood time and find the last meal time, subtract the time
difference and compute fractions of hours. xx.xx
Enter the formula in C2 and drag down through C9. Format C2 as a number with
2 places of decimal
to produce the times you show (8.00, 1.00, 1.00, 2.00, 8.50, 1.75, 1.25 and
2.15)
Your dates and times in your meal table must be in ascending sequence as
they already are.

=(BLOOD!A2-VLOOKUP(BLOOD!A2,MEALS!$A$2:$A$9,1))*24

Tyro

 

[Tyro]

If your formula is on the BLOOD sheet then of course no need to reference
the blood sheet thus this will do:

=(A2-VLOOKUP(A2,MEALS!$A$2:$A$9,1))*24

 

[k1ngr]

Tyro,

That works - THANKS-A-MILLION!!!

I had never used Range_lookup=TRUE in VLOOKUP, only FALSE.

Great help,

Dick King

 

[k1ngr]

T,
Thanks for the help, but I guess I did't explain my problem clearly enough.
See Tyro's post.

Dick King