Difference between two date/time values
in [Functions]
|
From: Raj on 31 May 2010 11:30 Hi, Cell A1 has 21-04-10 07:00 PM Cell A2 has 06-06-10 07:52 AM (the dates are in the dd-mm-yy format) Cell A3 has the formula =A2-A1 for showing the differences in days and time and is formatted with the custom format: dd h:mm:ss The display in cell A3 is 14 12:52:00. While the difference in hours is okay, the difference in days should be more than 14 as there is are 31 days of May between the two dates. Is there another format to show the correct difference in days or should be done some other way? Please help. Thanks in advance. Regards, Raj
From: T. Valko on 31 May 2010 13:16 Do you really need the seconds? Your sample entries only show date h:mm. If using a custom format of dd h:mm:ss the max *displayed* number of days will be the number of days in the *current month*. So, you can't use this format if the number of days are expected to be >31. You'd have to use a formula. Assuming the date/time in A2 will *always* be >= the date/time in A1. =MAX(0,INT(A2-A1))&" "&TEXT(MOD(A2-A1,1),"h:mm") -- Biff Microsoft Excel MVP "Raj" <rspai9(a)gmail.com> wrote in message news:64353ea4-821c-4fd3-939d-0ae2337e44b6(a)u3g2000prl.googlegroups.com... > Hi, > > Cell A1 has 21-04-10 07:00 PM > Cell A2 has 06-06-10 07:52 AM > (the dates are in the dd-mm-yy format) > > Cell A3 has the formula =A2-A1 for showing the differences in days and > time and is formatted with the custom format: > dd h:mm:ss > > The display in cell A3 is 14 12:52:00. While the difference in hours > is okay, the difference in days should be more than 14 as there is are > 31 days of May between the two dates. Is there another format to show > the correct difference in days or should be done some other way? > Please help. > > Thanks in advance. > > Regards, > Raj > >
From: Gord Dibben on 31 May 2010 13:18 Formatted as a number =A2-A1 is 45.54 45.54 is the serial number for Tuesday, February 14, 1900 12:52:00PM That's why you get 14 12:52:00 Enter this formula in A3 =DATEDIF(A1,A2,"d")-1 & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" minutes ""ss"" seconds""") Returns 45 days 12 hours 52 minutes 00 seconds Gord Dibben MS Excel MVP On Mon, 31 May 2010 08:30:48 -0700 (PDT), Raj <rspai9(a)gmail.com> wrote: >Hi, > >Cell A1 has 21-04-10 07:00 PM >Cell A2 has 06-06-10 07:52 AM >(the dates are in the dd-mm-yy format) > >Cell A3 has the formula =A2-A1 for showing the differences in days and >time and is formatted with the custom format: >dd h:mm:ss > >The display in cell A3 is 14 12:52:00. While the difference in hours >is okay, the difference in days should be more than 14 as there is are >31 days of May between the two dates. Is there another format to show >the correct difference in days or should be done some other way? >Please help. > >Thanks in advance. > >Regards, >Raj >
From: "David Biddulph" groups [at] on 31 May 2010 15:28 Doesn't that formula go wrong if the time in A1 is earlier than the time in A2, Gord? Isn't it easier not to use DATEDIF, and just to use =INT(A2-A1) & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" ? -- David Biddulph "Gord Dibben" <gorddibbATshawDOTca> wrote in message news:lcp706l2ufhmg069s2q66e2k0demvb6qpo(a)4ax.com... > Formatted as a number =A2-A1 is 45.54 > > 45.54 is the serial number for Tuesday, February 14, 1900 12:52:00PM > > That's why you get 14 12:52:00 > > Enter this formula in A3 > > =DATEDIF(A1,A2,"d")-1 & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" > minutes ""ss"" seconds""") > > Returns 45 days 12 hours 52 minutes 00 seconds > > > Gord Dibben MS Excel MVP > > > > On Mon, 31 May 2010 08:30:48 -0700 (PDT), Raj <rspai9(a)gmail.com> wrote: > >>Hi, >> >>Cell A1 has 21-04-10 07:00 PM >>Cell A2 has 06-06-10 07:52 AM >>(the dates are in the dd-mm-yy format) >> >>Cell A3 has the formula =A2-A1 for showing the differences in days and >>time and is formatted with the custom format: >>dd h:mm:ss >> >>The display in cell A3 is 14 12:52:00. While the difference in hours >>is okay, the difference in days should be more than 14 as there is are >>31 days of May between the two dates. Is there another format to show >>the correct difference in days or should be done some other way? >>Please help. >> >>Thanks in advance. >> >>Regards, >>Raj >> >
From: T. Valko on 31 May 2010 17:47
Tweak... >Assuming the date/time in A2 will *always* be >= the date/time in A1. >=MAX(0,INT(A2-A1))&" "&TEXT(MOD(A2-A1,1),"h:mm") If the date/time in A2 will *always* be >= the date/time in A1 then we don't need the MAX function: =INT(A2-A1)&" "&TEXT(MOD(A2-A1,1),"h:mm") -- Biff Microsoft Excel MVP "T. Valko" <biffinpitt(a)comcast.net> wrote in message news:OvPDCUOALHA.5476(a)TK2MSFTNGP06.phx.gbl... > Do you really need the seconds? Your sample entries only show date h:mm. > > If using a custom format of dd h:mm:ss the max *displayed* number of days > will be the number of days in the *current month*. > > So, you can't use this format if the number of days are expected to be > >31. You'd have to use a formula. > > Assuming the date/time in A2 will *always* be >= the date/time in A1. > > =MAX(0,INT(A2-A1))&" "&TEXT(MOD(A2-A1,1),"h:mm") > > -- > Biff > Microsoft Excel MVP > > > "Raj" <rspai9(a)gmail.com> wrote in message > news:64353ea4-821c-4fd3-939d-0ae2337e44b6(a)u3g2000prl.googlegroups.com... >> Hi, >> >> Cell A1 has 21-04-10 07:00 PM >> Cell A2 has 06-06-10 07:52 AM >> (the dates are in the dd-mm-yy format) >> >> Cell A3 has the formula =A2-A1 for showing the differences in days and >> time and is formatted with the custom format: >> dd h:mm:ss >> >> The display in cell A3 is 14 12:52:00. While the difference in hours >> is okay, the difference in days should be more than 14 as there is are >> 31 days of May between the two dates. Is there another format to show >> the correct difference in days or should be done some other way? >> Please help. >> >> Thanks in advance. >> >> Regards, >> Raj >> >> > > |