Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting Time using mm:ss.00 format
I need to subtract the difference between two different times. For example:
1) In Cell A1 I have typed 03:24.65 2) In Cell B1, I have typed 05:12:55 I formatted the cells as: mm:ss.00. My formula in C1 is =A1-B1. It returns all pound signs (#######). Additionally, when you click in the enter field for cell A1 it displays 12:03:25 AM instead of what I typed in A1 which was 03:24.65. I must have cell C1 display the exact difference. Can anyone tell me the best function or IF statement to use to calculate this? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting Time using mm:ss.00 format
First, ignore the formula bar. You're not going to change that.
Second, after you subtract the larger from the smaller, you're going to end up with negative time. One way to show negative time is to change to the 1904 date system (tools|Options|calculation tab|check 1904 date system) But every date in your workbook will be off by 4 years and one day--and if you copy|paste dates between workbooks with different date systems, you'll want to fix the problem. Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|Click Add (or Subtract) depending on which workbook you want to fix. You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Miasha wrote: I need to subtract the difference between two different times. For example: 1) In Cell A1 I have typed 03:24.65 2) In Cell B1, I have typed 05:12:55 I formatted the cells as: mm:ss.00. My formula in C1 is =A1-B1. It returns all pound signs (#######). Additionally, when you click in the enter field for cell A1 it displays 12:03:25 AM instead of what I typed in A1 which was 03:24.65. I must have cell C1 display the exact difference. Can anyone tell me the best function or IF statement to use to calculate this? Thanks in advance. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting Time using mm:ss.00 format
Miasha,
=A1-B1 is subtracting the larger from the smaller, meaning the results will be negative. EL can't handle negative times. That is why you are getting the #####. Reverse the cell references and you calculation should work correctly: =B1-A1 A1 = 03:24.65 = 3 mins, 24 secs, 65 ms. As far as XL is concerned about time, 12 midnight is 0, high noon is 0.5....so 03:14.65 would round to 12:03:15. A2 = 05:12:55 = 5 hrs, 12 mins, 55 secs. HTH, Conan "Miasha" wrote in message ... I need to subtract the difference between two different times. For example: 1) In Cell A1 I have typed 03:24.65 2) In Cell B1, I have typed 05:12:55 I formatted the cells as: mm:ss.00. My formula in C1 is =A1-B1. It returns all pound signs (#######). Additionally, when you click in the enter field for cell A1 it displays 12:03:25 AM instead of what I typed in A1 which was 03:24.65. I must have cell C1 display the exact difference. Can anyone tell me the best function or IF statement to use to calculate this? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting Time using mm:ss.00 format
You can get negative times and negative dates if the workbook uses the 1904 date
system. Conan Kelly wrote: Miasha, =A1-B1 is subtracting the larger from the smaller, meaning the results will be negative. EL can't handle negative times. That is why you are getting the #####. Reverse the cell references and you calculation should work correctly: =B1-A1 A1 = 03:24.65 = 3 mins, 24 secs, 65 ms. As far as XL is concerned about time, 12 midnight is 0, high noon is 0.5....so 03:14.65 would round to 12:03:15. A2 = 05:12:55 = 5 hrs, 12 mins, 55 secs. HTH, Conan "Miasha" wrote in message ... I need to subtract the difference between two different times. For example: 1) In Cell A1 I have typed 03:24.65 2) In Cell B1, I have typed 05:12:55 I formatted the cells as: mm:ss.00. My formula in C1 is =A1-B1. It returns all pound signs (#######). Additionally, when you click in the enter field for cell A1 it displays 12:03:25 AM instead of what I typed in A1 which was 03:24.65. I must have cell C1 display the exact difference. Can anyone tell me the best function or IF statement to use to calculate this? Thanks in advance. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting Time using mm:ss.00 format
Thanks so much! This helped tremendously!
"Dave Peterson" wrote: First, ignore the formula bar. You're not going to change that. Second, after you subtract the larger from the smaller, you're going to end up with negative time. One way to show negative time is to change to the 1904 date system (tools|Options|calculation tab|check 1904 date system) But every date in your workbook will be off by 4 years and one day--and if you copy|paste dates between workbooks with different date systems, you'll want to fix the problem. Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|Click Add (or Subtract) depending on which workbook you want to fix. You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Miasha wrote: I need to subtract the difference between two different times. For example: 1) In Cell A1 I have typed 03:24.65 2) In Cell B1, I have typed 05:12:55 I formatted the cells as: mm:ss.00. My formula in C1 is =A1-B1. It returns all pound signs (#######). Additionally, when you click in the enter field for cell A1 it displays 12:03:25 AM instead of what I typed in A1 which was 03:24.65. I must have cell C1 display the exact difference. Can anyone tell me the best function or IF statement to use to calculate this? Thanks in advance. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting time in date format | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions |