| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding time after calculation
I have a spreadsheet where it calculates the time between two hours each day.
At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding time after calculation
Format it as [h]:mm
-- __________________________________ HTH Bob "mrrherrera" wrote in message ... I have a spreadsheet where it calculates the time between two hours each day. At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding time after calculation
If your hh:mm problem is when you exceed 24 hours, try [hh]:mm
-- David Biddulph mrrherrera wrote: I have a spreadsheet where it calculates the time between two hours each day. At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding time after calculation
I tried what you suggested. It did not work. Here is the formula
=TEXT(D9-C9,"[h]: mm") for the first cell. The second cell's is =TEXT(D10-C10,"[h]: mm'). I then did =Sum(H9:H10). It gave me a blank cell. "David Biddulph" wrote: If your hh:mm problem is when you exceed 24 hours, try [hh]:mm -- David Biddulph mrrherrera wrote: I have a spreadsheet where it calculates the time between two hours each day. At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding time after calculation
Don't use the =text() worksheet function.
Just do regular subtraction, but use a custom format: Format|cells|number tab|Custom [hh]:mm or whatever you want. If you use =text() then the cells contain text/strings. And =sum() will ignore them. mrrherrera wrote: I tried what you suggested. It did not work. Here is the formula =TEXT(D9-C9,"[h]: mm") for the first cell. The second cell's is =TEXT(D10-C10,"[h]: mm'). I then did =Sum(H9:H10). It gave me a blank cell. "David Biddulph" wrote: If your hh:mm problem is when you exceed 24 hours, try [hh]:mm -- David Biddulph mrrherrera wrote: I have a spreadsheet where it calculates the time between two hours each day. At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding time after calculation
I changed the custom format to [hh]:mm. I deleted TEXT for each cell. The
formula is just the subtraction of the two cells, =d9-c9. When I added the column down, it gave me an answer of 1.1, when the answer should be 28. HELP! "Dave Peterson" wrote: Don't use the =text() worksheet function. Just do regular subtraction, but use a custom format: Format|cells|number tab|Custom [hh]:mm or whatever you want. If you use =text() then the cells contain text/strings. And =sum() will ignore them. mrrherrera wrote: I tried what you suggested. It did not work. Here is the formula =TEXT(D9-C9,"[h]: mm") for the first cell. The second cell's is =TEXT(D10-C10,"[h]: mm'). I then did =Sum(H9:H10). It gave me a blank cell. "David Biddulph" wrote: If your hh:mm problem is when you exceed 24 hours, try [hh]:mm -- David Biddulph mrrherrera wrote: I have a spreadsheet where it calculates the time between two hours each day. At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding time after calculation
You won't see 1.1 if it's formatted as [hh]:mm
Check your sheet again. -- David Biddulph mrrherrera wrote: I changed the custom format to [hh]:mm. I deleted TEXT for each cell. The formula is just the subtraction of the two cells, =d9-c9. When I added the column down, it gave me an answer of 1.1, when the answer should be 28. HELP! "Dave Peterson" wrote: Don't use the =text() worksheet function. Just do regular subtraction, but use a custom format: Format|cells|number tab|Custom [hh]:mm or whatever you want. If you use =text() then the cells contain text/strings. And =sum() will ignore them. mrrherrera wrote: I tried what you suggested. It did not work. Here is the formula =TEXT(D9-C9,"[h]: mm") for the first cell. The second cell's is =TEXT(D10-C10,"[h]: mm'). I then did =Sum(H9:H10). It gave me a blank cell. "David Biddulph" wrote: If your hh:mm problem is when you exceed 24 hours, try [hh]:mm -- David Biddulph mrrherrera wrote: I have a spreadsheet where it calculates the time between two hours each day. At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding time after calculation
I don't understand.
If the number is formatted as [hh]:mm, how can the answer be 28? I'd understand 28:00, 00:28 or something like that. If you share the formula and the value in each of the cells that that formulas uses, it'll be easier to help. mrrherrera wrote: I changed the custom format to [hh]:mm. I deleted TEXT for each cell. The formula is just the subtraction of the two cells, =d9-c9. When I added the column down, it gave me an answer of 1.1, when the answer should be 28. HELP! "Dave Peterson" wrote: Don't use the =text() worksheet function. Just do regular subtraction, but use a custom format: Format|cells|number tab|Custom [hh]:mm or whatever you want. If you use =text() then the cells contain text/strings. And =sum() will ignore them. mrrherrera wrote: I tried what you suggested. It did not work. Here is the formula =TEXT(D9-C9,"[h]: mm") for the first cell. The second cell's is =TEXT(D10-C10,"[h]: mm'). I then did =Sum(H9:H10). It gave me a blank cell. "David Biddulph" wrote: If your hh:mm problem is when you exceed 24 hours, try [hh]:mm -- David Biddulph mrrherrera wrote: I have a spreadsheet where it calculates the time between two hours each day. At the end of the month, I want to be able to add up the calculated times. I've tried =Sum(a1:a31), but it does not work. Each column is formatted for time as HH:MM. I tried changing it to HH:MM:SS that didn't work either. Thanks in advance for your help. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop time - start time calculation | Excel Worksheet Functions | |||
| Time calculation (Subraction of Idle Time) | Excel Discussion (Misc queries) | |||
| Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
| Adding time to date-time formatted cell | Excel Discussion (Misc queries) | |||
| time calculation with military time | Excel Worksheet Functions | |||
Hybrid Mode
