Difference between two date

[Former Member]

Challenge             : It has to take care to display the difference value between the dates which comes under leap and non leap year

Thump rule          : Its basic mathematical principle that we learned in our school.

                        a) Convert all the units of measurement (Time) into single unit of measurement

                        b) Which unit we should use: Year or Month or Week or Day or Hour or Min or Sec. We should choose the lowest unit of measurement of         time  i.e Second because difference between two date also involve difference between date of present year and next tear

Example :  31/12/2014 1:59:48 and 01/01/2015 00:00:20 format (DD/MM/YYYY)

Formula:

Below are the list of formula to convert the date value to the lowest unit of measurement i.e seconds:

Scenario : Lets assume to calculate the difference of two date value Dimension of Date data type that is "Current day and time" and "Contract End Time"

Logic :  Convert all time unit to Seconds as given in below and add up all the seconds value. We are not converting month to seconds directly, there is explanation given below * in Month->Days->Sec formula

             Seconds->Sec=> S*1

             Minutes - > Sec  =>M * 60

             Hours - > Sec =>H*3600

             Year->Sec=> Y*365*24*3600

             Month->Days->Sec :

             We should not take month unit directly instead we take it as days as each month has different number of Days

               a) First we need to find the "Current day and time" and "Contract End Time" is leap year or non leap year

                    Formula : If Mod([Year];4)=0 Then 29 Else 28

Current Date time to Seconds

Converting the Current day and time into seconds

1. v_Curr Year No   =Year([Current day and time])
2. v_Curr Year in Sec==[v_Curr Year No]*365*24*3600
3. v_Curr Mon No =MonthNumberOfYear([Current day and time])
4. v_Curr Day of Mon= DayNumberOfMonth([Current day and time])
5. v_Curr Feb Month=If Mod([v_Curr Year No];4)=0 Then 29 Else 28
6. v_Curr Days = =If [v_Curr Mon No]=1 Then [v_Curr Day of Mon] Else If [v_Curr Mon No]=2 Then [v_Curr Day of Mon]+31 Else If [v_Curr Mon No]=3 Then [v_Curr Day of Mon]+[v_Feb Month]+31 Else If [v_Curr Mon No]=4 Then [v_Curr Day of Mon]+[v_Feb Month]+62 Else If [v_Curr Mon No]=5 Then [v_Curr Day of Mon]+[v_Feb Month]+92   Else If [v_Curr Mon No]=6 Then [v_Curr Day of Mon]+[v_Feb Month]+123 Else If [v_Curr Mon No]=7 Then [v_Curr Day of Mon]+[v_Feb Month]+153 Else If [v_Curr Mon No]=8 Then [v_Curr Day of Mon]+[v_Feb Month]+184 Else If [v_Curr Mon No]=9 Then [v_Curr Day of Mon]+[v_Feb Month]+215 Else If [v_Curr Mon No]=10 Then [v_Curr Day of Mon]+[v_Feb Month]+245 Else If [v_Curr Mon No]=11 Then [v_Curr Day of Mon]+[v_Feb Month]+276 Else If [v_Curr Mon No]=12 Then [v_Curr Day of Mon]+[v_Feb Month]+306
7. v_Curr Days in Sec=[v_Curr Days]*24*3600
8. v_Current Hour =FormatDate([Current day and time];"HH")
9. v_Curr Hr in Sec=ToNumber([v_Current Hour])*3600
10. v_Current Minute =FormatDate([Current day and time];"mm")
11. v_Curr Min in Sec=ToNumber([v_Current Minute])*60
12. v_Current Second =FormatDate([Current day and time];"ss")
13. v_Curr Sec=ToNumber([v_Current Second])
14. v_Curr Total Secs =[v_Curr Year in Sec]+[v_Curr Days in Sec]+[v_Curr Hr in Sec]+[v_Curr Min in Sec]+[v_Curr Sec]

Contract Date time to Seconds

Converting the "Contract End Time" and time into seconds

1. v_ContEnd Year No=Year([Contract End Time])
2. v_ContEnd Year in Sec=[v_ContEnd Year No]*365*24*3600
3. v_ContEnd Mon No=MonthNumberOfYear([Contract End Time])
4. v_ContEnd Day of Mon= DayNumberOfMonth([Contract End Time])
5. v_ContEnd Feb Month=If Mod([v_ContEnd Year No];4)=0 Then 29 Else 28
6. v_ContEnd Days=If [v_ContEnd Mon No]=1 Then [v_ContEnd Day of Mon] Else If [v_ContEnd Mon No]=2 Then [v_ContEnd Day of Mon]+31 Else If [v_ContEnd Mon No]=3 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+31 Else If [v_ContEnd Mon No]=4 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+62 Else If [v_ContEnd Mon No]=5 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+92 Else If [v_ContEnd Mon No]=6 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+123 Else If [v_ContEnd Mon No]=7 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+153 Else If [v_ContEnd Mon No]=8 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+184 Else If [v_ContEnd Mon No]=9 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+215 Else If [v_ContEnd Mon No]=10 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+245 Else If [v_ContEnd Mon No]=11 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+276 Else If [v_ContEnd Mon No]=12 Then [v_ContEnd Day of Mon]+[v_ContEnd Feb Month]+306
7. v_ContEnd Days in Sec=[v_ContEnd Days]*24*3600
8. v_ContEnd Hour=FormatDate([Contract End Time];"HH")
9. v_ContEnd Hr in Sec =ToNumber([v_ContEnd Hour])*3600
10. v_ContEnd Minute=FormatDate([Contract End Time];"mm")
11. v_ContEnd Min in Sec=ToNumber([v_ContEnd Minute])*60
12. v_ContEnd Second=FormatDate([Contract End Time];"ss")
13. v_ContEnd Sec=ToNumber([v_ContEnd Second])
14. v_ContEnd Total Secs =[v_ContEnd Year in Sec]+[v_ContEnd Days in Sec]+[v_ContEnd Hr in Sec]+[v_ContEnd Min in Sec]+[v_ContEnd Sec]60

Time difference between two date values = [v_ContEnd Total Secs]-[v_Curr Total Secs]

Where v_ContEnd Total Secs  greater than or equal to v_Curr Total Secs

The result value is in the unit of second