+ Start a Discussion
Mohan Raj 33Mohan Raj 33 

Why this formula having to provide the wrong answer?

IF((MOD(DATEVALUE(Calculating_Date__c)-DATE(1996,01,01), 7) < 5),

 (IF((NOW() > Calculating_Date__c),
 ((ROUND(12*(
(5*FLOOR((TODAY()-DATE(1996,01,01))/7) +
MIN(5, 
    MOD(TODAY()-DATE(1996,01,01), 7) +
    MIN(1, 24/12*(MOD(NOW()-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(Calculating_Date__c)-DATE(1996,01,01))/7) +
MIN(5, 
    MOD(DATEVALUE(Calculating_Date__c )-DATE(1996,01,01), 7) +
    MIN(1, 24/12*(MOD(Calculating_Date__c -DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
), 0))
+
IF( (21 > (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)))) || (9 < (VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2)))) , 
 (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)) - 9),0 )),
((ROUND(12*(
(5*FLOOR((TODAY()-DATE(1996,01,01))/7) +
MIN(5, 
    MOD(TODAY()-DATE(1996,01,01), 7) +
    MIN(1, 24/12*(MOD(NOW()-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(Calculating_Date__c )-DATE(1996,01,01))/7) +
MIN(5, 
    MOD(DATEVALUE(Calculating_Date__c )-DATE(1996,01,01), 7) +
    MIN(1, 24/12*(MOD(Calculating_Date__c -DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
), 0))
-
IF( (21 > (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)))) || (9 < (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)))) , 
 (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)) - 9), 0)))),

IF((NOW() >  Calculating_Date__c ),
(((
 (NOW() -  Calculating_Date__c ) * 5 -
 (MOD(DATEVALUE(Calculating_Date__c) - DATE(1970,1,4),7) - MOD(TODAY() - DATE(1970,1,4),7)) * 2
) / 7 -
IF(MOD(TODAY() - DATE(1970,1,4),7) = 6,1,0) -
IF(MOD( DATEVALUE(Calculating_Date__c) - DATE(1970,1,4),7) = 0,1,0))*12
+
(VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)) - 9)),
((
 (NOW() -  Calculating_Date__c ) * 5 -
 (MOD(DATEVALUE(Calculating_Date__c) - DATE(1970,1,4),7) - MOD(TODAY() - DATE(1970,1,4),7)) * 2
) / 7 -
IF(MOD(TODAY() - DATE(1970,1,4),7) = 6,1,0) -
IF(MOD( DATEVALUE(Calculating_Date__c) - DATE(1970,1,4),7) = 0,1,0))*12
-
(VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2)) - 9)))
here It's provide the wrong answer in the value to taking in the Calculating Date to the NOW()  here I show the example to be provide the understand clearly, To select the Calculating Date is 10/17/2016 4.27PM and the value of NOW() is 10/18/2016 4.28 PM then the output comes by the formula is 19 hours but in practically the calculating Date is to be started to be a time to measured from the given input time so here the actual time is 4.27  only  so the output is only12 hours only .
Here the problem means if selected to the any date to the NOW() value it's to be only added the value of the current hour(NOW() values hour only) it's doesn't start to calculate the hour from the Calculating Date time it's considered thus value in 12 hours constant.So I don't know to how change the value and to be calculated from the Calculating Date given values time.
So that's why I am confusing here  to rectify that time considered problem.
So please help me to solve this issue in here .For answer's thanks in advance.  Thanks Mohan
 
Rakesh ARakesh A
If you dont mind, its too confusing the above story, let me know what your expeting as out put so that I can help you to explain that logic