| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula for time between two events
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula for time between two events
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 -- Biff Microsoft Excel MVP "k1ngr" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula for time between two events
T,
Thanks for the help, but I guess I did't explain my problem clearly enough. See Tyro's post. Dick King "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "k1ngr" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula for time between two events
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 "k1ngr" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula for time between two events
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 "Tyro" wrote in message ... 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 "k1ngr" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need formula for time between two events
Tyro,
That works - THANKS-A-MILLION!!! I had never used Range_lookup=TRUE in VLOOKUP, only FALSE. Great help, Dick King "Tyro" wrote: 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 "Tyro" wrote in message ... 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 "k1ngr" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Chart for events per unit of time??? | Charts and Charting in Excel | |||
| How can I calculate events that happened at the same time? | Excel Discussion (Misc queries) | |||
| Finding concurrent events from a list with a start and an end time | Excel Discussion (Misc queries) | |||
| I need to measure hours between two events(time/date) to give me . | Excel Worksheet Functions | |||
| Allowing events/macros during formula mode? | Excel Discussion (Misc queries) | |||
Hybrid Mode
