| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Calculations as Days
I would like the end result to include Days, Hours, Minutes and Seconds.
In one cell I have the first date: 7/19/2006 2:54:51 PM In a second cell I have the end date: 10/5/2006 2:12:06 PM I'm trying to calculate the difference but I don't want Months, Months should be counted as Days. I tried the following formula and I thought it was going to work but it doesn't ... =DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & " Minutes " & SECOND(D8-C8) & " Seconds " The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it should be 77 Days not 78 Days. The number of Days is 78 if you don't take into account the time but because of the time it is not technically 78 Days yet. Any thoughts? Please! Help! Please! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Calculations as Days
On Tue, 20 Nov 2007 10:05:00 -0800, FrankM
wrote: I would like the end result to include Days, Hours, Minutes and Seconds. In one cell I have the first date: 7/19/2006 2:54:51 PM In a second cell I have the end date: 10/5/2006 2:12:06 PM I'm trying to calculate the difference but I don't want Months, Months should be counted as Days. I tried the following formula and I thought it was going to work but it doesn't ... =DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & " Minutes " & SECOND(D8-C8) & " Seconds " The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it should be 77 Days not 78 Days. The number of Days is 78 if you don't take into account the time but because of the time it is not technically 78 Days yet. Any thoughts? Please! Help! Please! A1: start date/time A2: end date/time =INT(A2-A1)&" days, " & TEXT(MOD(A2-A1,1),"h"" hrs ""mm"" min ""ss"" sec""") --ron |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Calculations as Days
ONe way:
=INT(D8 - C8) & " Days " & .... In article , FrankM wrote: I would like the end result to include Days, Hours, Minutes and Seconds. In one cell I have the first date: 7/19/2006 2:54:51 PM In a second cell I have the end date: 10/5/2006 2:12:06 PM I'm trying to calculate the difference but I don't want Months, Months should be counted as Days. I tried the following formula and I thought it was going to work but it doesn't ... =DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & " Minutes " & SECOND(D8-C8) & " Seconds " The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it should be 77 Days not 78 Days. The number of Days is 78 if you don't take into account the time but because of the time it is not technically 78 Days yet. Any thoughts? Please! Help! Please! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Calculations as Days
Check the time to see if you need to subtract a day or not... Something like
this... =DATEDIF(C8,D8,"d")-IF(MOD(C8,1)MOD(D8,1),1,0)&" Days "&HOUR(D8-C8)&" Hours "&MINUTE(D8-C8)&" Minutes "&SECOND(D8-C8)&" Seconds " -- HTH... Jim Thomlinson "FrankM" wrote: I would like the end result to include Days, Hours, Minutes and Seconds. In one cell I have the first date: 7/19/2006 2:54:51 PM In a second cell I have the end date: 10/5/2006 2:12:06 PM I'm trying to calculate the difference but I don't want Months, Months should be counted as Days. I tried the following formula and I thought it was going to work but it doesn't ... =DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & " Minutes " & SECOND(D8-C8) & " Seconds " The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it should be 77 Days not 78 Days. The number of Days is 78 if you don't take into account the time but because of the time it is not technically 78 Days yet. Any thoughts? Please! Help! Please! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Calculations as Days
AWESOME. It worked perfectly! Thank you.
"Ron Rosenfeld" wrote: On Tue, 20 Nov 2007 10:05:00 -0800, FrankM wrote: I would like the end result to include Days, Hours, Minutes and Seconds. In one cell I have the first date: 7/19/2006 2:54:51 PM In a second cell I have the end date: 10/5/2006 2:12:06 PM I'm trying to calculate the difference but I don't want Months, Months should be counted as Days. I tried the following formula and I thought it was going to work but it doesn't ... =DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & " Minutes " & SECOND(D8-C8) & " Seconds " The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it should be 77 Days not 78 Days. The number of Days is 78 if you don't take into account the time but because of the time it is not technically 78 Days yet. Any thoughts? Please! Help! Please! A1: start date/time A2: end date/time =INT(A2-A1)&" days, " & TEXT(MOD(A2-A1,1),"h"" hrs ""mm"" min ""ss"" sec""") --ron |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Calculations as Days
AWESOME. It worked perfectly! Thank you.
I used this one and it was great! "JE McGimpsey" wrote: ONe way: =INT(D8 - C8) & " Days " & .... In article , FrankM wrote: I would like the end result to include Days, Hours, Minutes and Seconds. In one cell I have the first date: 7/19/2006 2:54:51 PM In a second cell I have the end date: 10/5/2006 2:12:06 PM I'm trying to calculate the difference but I don't want Months, Months should be counted as Days. I tried the following formula and I thought it was going to work but it doesn't ... =DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & " Minutes " & SECOND(D8-C8) & " Seconds " The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it should be 77 Days not 78 Days. The number of Days is 78 if you don't take into account the time but because of the time it is not technically 78 Days yet. Any thoughts? Please! Help! Please! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Calculations as Days
This way did seem to work. Thank you for your reply, I'm gonna havta tuck
this one away in my memory. But I decided to use the INT calculation. There seems to be more than one way to accomplish things. Thank you! "Jim Thomlinson" wrote: Check the time to see if you need to subtract a day or not... Something like this... =DATEDIF(C8,D8,"d")-IF(MOD(C8,1)MOD(D8,1),1,0)&" Days "&HOUR(D8-C8)&" Hours "&MINUTE(D8-C8)&" Minutes "&SECOND(D8-C8)&" Seconds " -- HTH... Jim Thomlinson "FrankM" wrote: I would like the end result to include Days, Hours, Minutes and Seconds. In one cell I have the first date: 7/19/2006 2:54:51 PM In a second cell I have the end date: 10/5/2006 2:12:06 PM I'm trying to calculate the difference but I don't want Months, Months should be counted as Days. I tried the following formula and I thought it was going to work but it doesn't ... =DATEDIF(C8,D8,"d") & " Days "& HOUR(D8-C8) & " Hours " & MINUTE(D8-C8) & " Minutes " & SECOND(D8-C8) & " Seconds " The response is 78 Days 23 Hours 17 Minutes 15 Seconds but in reality it should be 77 Days not 78 Days. The number of Days is 78 if you don't take into account the time but because of the time it is not technically 78 Days yet. Any thoughts? Please! Help! Please! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| Inconsistent excel 2007b2 time calculations that span whole days | Excel Discussion (Misc queries) | |||
| Time calculations and additional 24 hour days | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Linear Mode
