# How to convert this formula for only working hours in weekdays (working hours: 9AM - 9PM)

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).

• 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. Oct 17 '16 at 16:28

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