Problem finding Number of Days between 2 dates

[system]

Hi All

I tried using the below resolution provided in the knowledge base.
But I am having problems.
I am getting -ve values once I use the below process.
Here the formula is taking the difference as Minutes - minutes, hours - hours, seconds - seconds.
It is not considering the the hours while subtracting the minutes and minutes while subtracting the seconds.

1. Create a variable Days to get the number of days between the two dates.
   Number of
   day:
   =DaysBetween( ,)

2. To get the number of hours:

=ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,1 ,2))-
ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,1 ,2))

3. To get the number of Minutes:

=ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,4 ,2))-
ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,4 ,2))

4. To get the number of Seconds:

=ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,7
,2))- ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,7 ,2))

5. Concatenate these variables like:

=+":"++":"++":"+

Hope I am clear in my explanation.

THanks in advance.
B…

---

bobjkb (BOB member since 2004-03-19)

[system]

Hi,

You’re right, that solution does nothing to calculate the remaining hours, minutes, seconds, etc.

You will need to determine the number of days, then take the whole number and subtract that from the total. Use the remainder in the calculation for hours. Then take the whole number from that and subtract from the total. Take the remainder from that and use in the calculation for minutes. Keep going until you have calculated the number of seconds.

Judy

---

JMulders (BOB member since 2002-06-20)

[system]

Thanks a lot for the reply.
Could you please let us know how exactly this can be done ?

B…

---

bobjkb (BOB member since 2004-03-19)

[system]

5. Concatenate these variables like:

=+":"++":"++":"+

Try,

=&":"&&":"&&":"&

---

KhoushikTTT (BOB member since 2005-02-24)

[system]

Hey Neo

That is where I am getting the -ve values.

[Moderator Edit: Please avoid obsure abbreviations. Please spell out negative rather than -ve, for example.]

B…

---

bobjkb (BOB member since 2004-03-19)

[system]

Hi,
This Solution might help…to calculate the number of hours between two dates…May need to change a bit to get seconds…

FAQ: Designer

How can I calculate the number of hours between two dates? The best way to do it (and the fastest) is to do it in the database. If you simply subtract the two days, you will get a number in the format D.T, where “D” is the number of days, and “T” is the fraction of days in decimal form. In other words, day 2 and noon minus day 1 at midnight will yield 1.5 as the result. Take this number and multiply by 24, that gives you the number of hours. Take the decimal portion of that and divide …

Ravi

---

ravi_prk (BOB member since 2004-09-03)

[system]

Hi B…

This is a bit dumb but it works.

Diff gives the difference between the dates.

Date 1 (dec 1 2005)is allways an earlier date compared to Date2(dec 10 2005)

=DaysBetween( ,)

=(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,1 ,2))*3600)+(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,4 ,2))*60)+(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,7
,2)))

=(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,1 ,2))*3600)+(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,4 ,2))*60)+(ToNumber(SubStr(FormatDate( ,“HH:mm:ss”) ,7
,2)))

= If (>) Then (Abs()+“d:”+(Floor((-)/3600))+“h:”+Floor(Mod((-) ,3600)/60)+“m:”+Mod((Mod((-) ,3600)) ,60)+“s”) Else (Abs(+1)+“d:”+(Floor(Abs((+86400)-)/3600))+“h:”+Floor(Mod(((+86400)-) ,3600)/60)+“m:”+Mod((Mod(((+86400)-) ,3600)) ,60)+“s”)

• YSS -

---

yss (BOB member since 2005-02-26)