0

Here I am trying to calculate the difference of Two date/time fields in hour manner.

here the formula is to be calculated over the both the past and future values i.e it's to be provided the difference in two date/time values in hours for both past and future days also.but the identifying purpose it's provided the future date difference is in the values are in negative form.

As the using formula is to be followingly,

CASE( (VALUE(TEXT(DATEVALUE(NOW()) - DATEVALUE(Calculating_Date__c)))),
-2,(VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2))),
-3,((24 - VALUE(MID(RIGHT((TEXT( Calculating_Date__c  + 0.2291)),9),0,2)))
+
VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2))*2),
-4,((((24 - VALUE(MID(RIGHT((TEXT( Calculating_Date__c  + 0.2291)),9),0,2)))
+
VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2)))*2)+ VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2)) ),
-5,((((24 - VALUE(MID(RIGHT((TEXT( Calculating_Date__c  + 0.2291)),9),0,2)))
+
VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2)))*3)+ VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2))
),
-6,((((24 - VALUE(MID(RIGHT((TEXT( Calculating_Date__c  + 0.2291)),9),0,2)))
+
VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2)))*4) + VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2))),
(((((5 * ( FLOOR( (  DATEVALUE(NOW())  - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(  DATEVALUE(NOW())  - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( (  DATEVALUE(Calculating_Date__c)  - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(  (DATEVALUE(Calculating_Date__c)  - DATE( 1900, 1, 8)), 7 ) )))-1)*24)
+
(24 - VALUE(MID(RIGHT((TEXT( Calculating_Date__c  + 0.2291)),9),0,2)))
+
VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2)))
)

So here I want now the same formula only calculate the working hours on the week days like only calculation time in hours is 9.00 AM to 9.00 PM only (in here I given formula is to be calculated in all hours(24 hours) in a week days).

For answer thanks in advance and help me to solve this task.

  • Should it take into account Daylight Saving Time? – joe Oct 17 '16 at 16:01
  • No, account Day light saving time it's to be calculated only in between the Calculation date and the NOW() values as given as like an example if the value of the Calculation date is 10/10/2016 and the Now() value is 10/17/2016 then it's to be answer is 5 days as per 60 hours and the 10/17/2016 is a monday so it add to the monday's value to like the time on 4 hours means the answer should be 64 hours in finally. – MohanRaj Oct 17 '16 at 16:28
0

to all, the one of the answer of my question is followingly,

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

(IF((NOW() > Calculating_Date__c), 

(ROUND(( NOW() - Calculating_Date__c),0) 
* 
IF( (21 > (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)))) || (9 < (VALUE(MID(RIGHT((TEXT( NOW() + 0.2291)),9),0,2)))) , 
(12-(VALUE(MID(RIGHT((TEXT(Calculating_Date__c + 0.2291)),9),0,2)) - 9)) + 
(VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)) - 9),Null )), 

(( Calculating_Date__c - NOW()) 
* 
IF( (21 > (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)))) || (9 < (VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)))) , 
(12-(VALUE(MID(RIGHT((TEXT(Calculating_Date__c + 0.2291)),9),0,2)) - 9)) + 
(VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)) - 9), 0)))), 

IF((NOW() > Calculating_Date__c ), 
(ROUND((( 
(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,0) 
+ 
(VALUE(MID(RIGHT((TEXT(NOW() + 0.2291)),9),0,2)) - 9)), 
(ROUND(( ( 
(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 ,0) 
+ 
(VALUE(MID(RIGHT((TEXT( NOW() + 0.2291)),9),0,2)) - 9))))

Thank you, Mohan

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.