| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate time
Mark,
Try something like =(EndTime - StartTime)+(StartTimeEndTime) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... I have to keep time log for my business travel within the city. Does anyone know of a formula that will calculate the difference of a start time and an end time. Pleas help! MRuiz |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate time
Mark,
The second half of the formula, +(StartTimeEndTime), is used to correct the value if the StartTime is before midnight and the EndTime is after midnight; for example, if you start at 9PM and end at 3AM. Times are stored as a fraction of a 24 hour day, so 9PM is stored as 0.875 and 3AM is stored as 0.125. If you simply subtract EndTime - StartTime, you'll end up with 0.125 - 0.875, or -0.75. This is remedied by the second part of the formula. If StartTime is greater than EndTime (9PM is later than 3AM), the comparison StartTimeEndTime returns TRUE which is treated as 1. This adds 1, the equivalent of 24 hours, to the result, which makes the example formula 0.125 - 0.875 + 1 = 0.25, or 6:00. In effect, the +(StartTimeEndTime) moves the EndTime in to the following day. For lots more information about working with dates and times in Excel, see www.cpearson.com/excel/datetime.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... Thanks Chip, That work perfect! I really don't understand the second part of that formula but it works just fine. Thnx for your time, MRuiz "Chip Pearson" wrote in message ... Mark, Try something like =(EndTime - StartTime)+(StartTimeEndTime) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... I have to keep time log for my business travel within the city. Does anyone know of a formula that will calculate the difference of a start time and an end time. Pleas help! MRuiz |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate time
Mark,
Specifically what problems are you having when you try to sum the times? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... Sorry Chip, I can't get the sum of the individual times. Thnx, MRuiz "Chip Pearson" wrote in message ... Mark, Try something like =(EndTime - StartTime)+(StartTimeEndTime) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... I have to keep time log for my business travel within the city. Does anyone know of a formula that will calculate the difference of a start time and an end time. Pleas help! MRuiz |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate time
I have to keep time log for my business travel within the city. Does anyone
know of a formula that will calculate the difference of a start time and an end time. Pleas help! MRuiz |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate time
Thanks Chip,
That work perfect! I really don't understand the second part of that formula but it works just fine. Thnx for your time, MRuiz "Chip Pearson" wrote in message ... Mark, Try something like =(EndTime - StartTime)+(StartTimeEndTime) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... I have to keep time log for my business travel within the city. Does anyone know of a formula that will calculate the difference of a start time and an end time. Pleas help! MRuiz |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate time
Sorry Chip,
I can't get the sum of the individual times. Thnx, MRuiz "Chip Pearson" wrote in message ... Mark, Try something like =(EndTime - StartTime)+(StartTimeEndTime) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... I have to keep time log for my business travel within the city. Does anyone know of a formula that will calculate the difference of a start time and an end time. Pleas help! MRuiz |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate time
Chip,
It was a formatting issue. Thanks for helping me out. This will really save me alot of time. Thnx, MRuiz "Chip Pearson" wrote in message ... Mark, Specifically what problems are you having when you try to sum the times? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... Sorry Chip, I can't get the sum of the individual times. Thnx, MRuiz "Chip Pearson" wrote in message ... Mark, Try something like =(EndTime - StartTime)+(StartTimeEndTime) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mark Ruiz" wrote in message ... I have to keep time log for my business travel within the city. Does anyone know of a formula that will calculate the difference of a start time and an end time. Pleas help! MRuiz |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 calculate time between 2 date/time columns | Excel Discussion (Misc queries) | |||
| calculate from time started and finished to get total time used | Excel Worksheet Functions | |||
| Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
| IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
| how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
Linear Mode
