Hello formula experts, have been trying but not getting the formula to only calculate business hours between these 2 dates:
1) Created Date (Date/Time)
2) Current Date/Time
This is the formula I am currently using:
ROUND( 11 * ( ( 5 * FLOOR( ( DATEVALUE( NOW() ) - DATE( 1900, 1, 8) ) / 7) + MIN(5, MOD( DATEVALUE( NOW() ) - DATE( 1900, 1, 8), 7) + MIN( 1, 24 / 11 * ( MOD( NOW() - DATETIMEVALUE( '1900-01-08 20:00:00' ), 1 ) ) ) ) ) - ( 5 * FLOOR( ( DATEVALUE( CreatedDate ) - DATE( 1900, 1, 8) ) / 7) + MIN( 5, MOD( DATEVALUE( CreatedDate ) - DATE( 1996, 1, 1), 7 ) + MIN( 1, 24 / 11 * ( MOD( CreatedDate - DATETIMEVALUE( '1900-01-08 20:00:00' ), 1) ) ) ) ) ), 0 )
However, it is giving me really weird results (ie: - hours etc...)
Can someone please guide or amend the formula? I reckon it has something to do with the GMT to AEDT conversion??
Forum Ambassador
Eric Praud (Activ8 Solar Energies)I really am an idiot sometimes... I got confused with the GMT+10.5 and the 11 hour day (7 AM to 6 PM).
Try this
((TODAY()-DATEVALUE(CreatedDate)-1)*11
+
((18*60+00)
-
(
IF(CASE(WEEKDAY(DATEVALUE(CreatedDate )),1,1,7,1,0)=1,7*60+00,
MIN(18*60+00,
MAX((HOUR(TIMEVALUE(CreatedDate))+IF(HOUR(TIMEVALUE(CreatedDate))-13.5<0,10.5,-13.5))*60+MINUTE(TIMEVALUE(CreatedDate)),7*60+00)))))/60
+
(
IF(
CASE(WEEKDAY(TODAY()),1,1,7,1,0)=1,18*60+0,
MIN((MAX(7*60+00,(HOUR(TIMEVALUE(NOW()))+IF(HOUR(TIMEVALUE(NOW()))-13.5<0,10.5,-13.5))*60+MINUTE(TIMEVALUE(NOW())))),(18*60+0)))
-
(7*60+00))/60)
-
11*(
FLOOR((TODAY()- DATEVALUE(CreatedDate ))/7)*2
+
IF(AND(WEEKDAY(DATEVALUE(CreatedDate ))=1, WEEKDAY(TODAY())<>7),1,
IF(CASE(WEEKDAY(DATEVALUE(CreatedDate )),1,8,WEEKDAY(DATEVALUE(CreatedDate )))>CASE(WEEKDAY(TODAY()),1,8,WEEKDAY(TODAY())),2,
IF(OR (WEEKDAY(TODAY())=7, WEEKDAY(DATEVALUE(CreatedDate ))=1),1,
IF(OR (WEEKDAY(TODAY())=1, WEEKDAY(DATEVALUE(CreatedDate ))=7),2,
0))))
)
