Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Calculating Time Elapsed in Excel
Hi Everyone,
How can I calculate the time elapsed between two given times? For example if I input initial time and date as 20:00 on 03 Jan 06 and final time and date as 05:00 on 05 Jan 06, I want to calculate the number of hours elapsed. Moreover I am interested in whole numbers i.e. 8 hours and 31 minutes should round to 9 hours and 8 hours and 29 minutes should round to 8 hours. I need to multiply this time elapsed with another constant quantity? Any help would be appreciated. regards |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Calculating Time Elapsed in Excel
Hi,
Assuming dates are held in format dd/mm/yyyy HH:MM then simple subtract two dates and multiply by 24. e.g in c1 put = INT((B1-A1)*24+0.5) 03/06/2006 20:00 (A1) 05/06/2006 20:29 (B1) C1=48 HTH " wrote: Hi Everyone, How can I calculate the time elapsed between two given times? For example if I input initial time and date as 20:00 on 03 Jan 06 and final time and date as 05:00 on 05 Jan 06, I want to calculate the number of hours elapsed. Moreover I am interested in whole numbers i.e. 8 hours and 31 minutes should round to 9 hours and 8 hours and 29 minutes should round to 8 hours. I need to multiply this time elapsed with another constant quantity? Any help would be appreciated. regards |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Calculating Time Elapsed in Excel
Thanks Toppers.
I have copied the formula to other cells in the column. The only problem is that when there is nothing in the first two columns, I get 0.00. in the third column. Is it possible that these cells remain blank untill I enter dates/times in the first two columns? Thanks |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Calculating Time Elapsed in Excel
Hi,
Ig I understand correctly, in third column you could put: =IF(Sum(a2:b2)=0, " ", sum(a2:b2)) " wrote: Thanks Toppers. I have copied the formula to other cells in the column. The only problem is that when there is nothing in the first two columns, I get 0.00. in the third column. Is it possible that these cells remain blank untill I enter dates/times in the first two columns? Thanks |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Calculating Time Elapsed in Excel
Hi,
Sorry, ignore my previous reply! Should be: =IF(OR(A1="",B1=""),"",INT((B1-A1)*24+0.5)) "Toppers" wrote: Hi, Assuming dates are held in format dd/mm/yyyy HH:MM then simple subtract two dates and multiply by 24. e.g in c1 put = INT((B1-A1)*24+0.5) 03/06/2006 20:00 (A1) 05/06/2006 20:29 (B1) C1=48 HTH " wrote: Hi Everyone, How can I calculate the time elapsed between two given times? For example if I input initial time and date as 20:00 on 03 Jan 06 and final time and date as 05:00 on 05 Jan 06, I want to calculate the number of hours elapsed. Moreover I am interested in whole numbers i.e. 8 hours and 31 minutes should round to 9 hours and 8 hours and 29 minutes should round to 8 hours. I need to multiply this time elapsed with another constant quantity? Any help would be appreciated. regards |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Calculating Time Elapsed in Excel
|
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Calculating Time Elapsed in Excel
Thanks Pete but I will stick to Toppers solution at this time. Its
doing what I want. One thing that I need now is to lock the column containing the formula so the user cannot change it . I want the user to enter the date and time but prevent him from deleting the formula accidentally. Thanks and regards |
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Calculating Time Elapsed in Excel
Thanks Pete but I will stick to Toppers solution at this time. Its
doing what I want. One thing that I need now is to lock the column containing the formula so the user cannot change it . I want the user to enter the date and time but prevent him from deleting the formula accidentally. Thanks and regards |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Time Elapsed in Excel
Hi Why don't you just do the following Sub main() Cells(1, 1).NumberFormat = "[h]:mm:ss;@" Cells(1, 1) = Format(Time, "hh:mm:ss") Call Functioname(x, y, z) Cells(2, 1).NumberFormat = "[h]:mm:ss;@" Cells(2, 1) = Format(Time, "hh:mm:ss") Timee = Cells(2, 1) - Cells(1, 1) Cells(2, 1).NumberFormat = "[h]:mm:ss;@" Cells(3, 1) = Format(Timee, "hh:mm:ss") End Sub -- karnak ------------------------------------------------------------------------ karnak's Profile: http://www.excelforum.com/member.php...o&userid=28918 View this thread: http://www.excelforum.com/showthread...hreadid=502368 |
#10
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Calculating Time Elapsed in Excel
|
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Calculating Time Elapsed in Excel
Try using the cell format [h]. You may need to make it in the custom option
in format cells. " wrote: Hi Everyone, How can I calculate the time elapsed between two given times? For example if I input initial time and date as 20:00 on 03 Jan 06 and final time and date as 05:00 on 05 Jan 06, I want to calculate the number of hours elapsed. Moreover I am interested in whole numbers i.e. 8 hours and 31 minutes should round to 9 hours and 8 hours and 29 minutes should round to 8 hours. I need to multiply this time elapsed with another constant quantity? Any help would be appreciated. regards |
#12
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Calculating Time Elapsed in Excel
Thanks for the help guys.
regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Elapsed Time | Excel Worksheet Functions | |||
Calculating elapsed time | Excel Discussion (Misc queries) | |||
Calculating Time elapsed | Excel Worksheet Functions | |||
Calculating elapsed time | Excel Worksheet Functions | |||
Calculating a rate for elapsed time? | Excel Discussion (Misc queries) |