| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time calculation problem (URGENTProject due)
I'm trying to find the difference in date and time witht the respective input
data in four columns (see below). I have attempted to combine the contents of each respective date with its respective times to get the following results with the differene of the combinations in cell G1. I have formatted cells a1, c1, wih mm/dd/yy, and b1, d1 as general. Cells e1,f1 are formatted with time format: mm/dd/yy 00:00. The method to combine the data fields was: =A1&" "&B1 (same for C1, D1). Why am I not getting the time difference in G1? Why is the data combination results not showing in defined format? Are there any other ways to get the time differences? I will have dates that go past 24 hours, so any methods to incorporate that as well is much appreciated. A1 B1 C1 D1 E1 F1 G1 1/2/05 2000 1/4/05 2200 38354 2000 38356 2200 #VALUE! THANKS MUCH! -- gman |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time calculation problem (URGENTProject due)
This should get you in the right direction:
Excel counts TIME as a decimal fraction of a DAY. So 1:00 AM is 1/24 or 0.0416666666666667 In your case, 2200 needs to be 2200/2400 What you need to do is: cell E1: =A1+(B1/2400) Does that help? *********** Regards, Ron "g6pack" wrote: I'm trying to find the difference in date and time witht the respective input data in four columns (see below). I have attempted to combine the contents of each respective date with its respective times to get the following results with the differene of the combinations in cell G1. I have formatted cells a1, c1, wih mm/dd/yy, and b1, d1 as general. Cells e1,f1 are formatted with time format: mm/dd/yy 00:00. The method to combine the data fields was: =A1&" "&B1 (same for C1, D1). Why am I not getting the time difference in G1? Why is the data combination results not showing in defined format? Are there any other ways to get the time differences? I will have dates that go past 24 hours, so any methods to incorporate that as well is much appreciated. A1 B1 C1 D1 E1 F1 G1 1/2/05 2000 1/4/05 2200 38354 2000 38356 2200 #VALUE! THANKS MUCH! -- gman |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time calculation problem (URGENTProject due)
I actually found another post that guided me to convert the time (b1, d1) in
text format to a time format by using: =--TEXT(A1,"00\:00\"). I then added the new time columns to the old date columns to get new date/time columns. AFter subtracting the new respective date/time columns, I actualy got an hourly difference; however, it did not compensate for the differnce in date. How do I incoporate going over 24 hours for a date/time difference -- gman "Ron Coderre" wrote: This should get you in the right direction: Excel counts TIME as a decimal fraction of a DAY. So 1:00 AM is 1/24 or 0.0416666666666667 In your case, 2200 needs to be 2200/2400 What you need to do is: cell E1: =A1+(B1/2400) Does that help? *********** Regards, Ron "g6pack" wrote: I'm trying to find the difference in date and time witht the respective input data in four columns (see below). I have attempted to combine the contents of each respective date with its respective times to get the following results with the differene of the combinations in cell G1. I have formatted cells a1, c1, wih mm/dd/yy, and b1, d1 as general. Cells e1,f1 are formatted with time format: mm/dd/yy 00:00. The method to combine the data fields was: =A1&" "&B1 (same for C1, D1). Why am I not getting the time difference in G1? Why is the data combination results not showing in defined format? Are there any other ways to get the time differences? I will have dates that go past 24 hours, so any methods to incorporate that as well is much appreciated. A1 B1 C1 D1 E1 F1 G1 1/2/05 2000 1/4/05 2200 38354 2000 38356 2200 #VALUE! THANKS MUCH! -- gman |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time calculation problem (URGENTProject due)
To display more than 24 hours, use a format of [hh]:mm
-- Regards, Fred "g6pack" wrote in message ... I actually found another post that guided me to convert the time (b1, d1) in text format to a time format by using: =--TEXT(A1,"00\:00\"). I then added the new time columns to the old date columns to get new date/time columns. AFter subtracting the new respective date/time columns, I actualy got an hourly difference; however, it did not compensate for the differnce in date. How do I incoporate going over 24 hours for a date/time difference -- gman "Ron Coderre" wrote: This should get you in the right direction: Excel counts TIME as a decimal fraction of a DAY. So 1:00 AM is 1/24 or 0.0416666666666667 In your case, 2200 needs to be 2200/2400 What you need to do is: cell E1: =A1+(B1/2400) Does that help? *********** Regards, Ron "g6pack" wrote: I'm trying to find the difference in date and time witht the respective input data in four columns (see below). I have attempted to combine the contents of each respective date with its respective times to get the following results with the differene of the combinations in cell G1. I have formatted cells a1, c1, wih mm/dd/yy, and b1, d1 as general. Cells e1,f1 are formatted with time format: mm/dd/yy 00:00. The method to combine the data fields was: =A1&" "&B1 (same for C1, D1). Why am I not getting the time difference in G1? Why is the data combination results not showing in defined format? Are there any other ways to get the time differences? I will have dates that go past 24 hours, so any methods to incorporate that as well is much appreciated. A1 B1 C1 D1 E1 F1 G1 1/2/05 2000 1/4/05 2200 38354 2000 38356 2200 #VALUE! THANKS MUCH! -- gman |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time calculation problem (URGENTProject due)
Perfect!!! Thanks Fred!
-- gman "Fred Smith" wrote: To display more than 24 hours, use a format of [hh]:mm -- Regards, Fred "g6pack" wrote in message ... I actually found another post that guided me to convert the time (b1, d1) in text format to a time format by using: =--TEXT(A1,"00\:00\"). I then added the new time columns to the old date columns to get new date/time columns. AFter subtracting the new respective date/time columns, I actualy got an hourly difference; however, it did not compensate for the differnce in date. How do I incoporate going over 24 hours for a date/time difference -- gman "Ron Coderre" wrote: This should get you in the right direction: Excel counts TIME as a decimal fraction of a DAY. So 1:00 AM is 1/24 or 0.0416666666666667 In your case, 2200 needs to be 2200/2400 What you need to do is: cell E1: =A1+(B1/2400) Does that help? *********** Regards, Ron "g6pack" wrote: I'm trying to find the difference in date and time witht the respective input data in four columns (see below). I have attempted to combine the contents of each respective date with its respective times to get the following results with the differene of the combinations in cell G1. I have formatted cells a1, c1, wih mm/dd/yy, and b1, d1 as general. Cells e1,f1 are formatted with time format: mm/dd/yy 00:00. The method to combine the data fields was: =A1&" "&B1 (same for C1, D1). Why am I not getting the time difference in G1? Why is the data combination results not showing in defined format? Are there any other ways to get the time differences? I will have dates that go past 24 hours, so any methods to incorporate that as well is much appreciated. A1 B1 C1 D1 E1 F1 G1 1/2/05 2000 1/4/05 2200 38354 2000 38356 2200 #VALUE! THANKS MUCH! -- gman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| perform calculation only if current time is after 10 am | Excel Worksheet Functions | |||
| Time Date Formula Problem | Excel Worksheet Functions | |||
| Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
| Variable time calculation | New Users to Excel | |||
| Time Calculation | New Users to Excel | |||
Linear Mode
