IF (
and (Value( MID(TEXT( End_of_Shift__c ),12,2)) <5, Value( MID(TEXT(
Start_of_the_shift__c ),12,2)) <5 ),( End_of_Shift__c -
Start_of_the_shift__c ) * 24,
IF
(and (Value( MID(TEXT( End_of_Shift__c ),12,2)) >=5, Value( MID(TEXT(
Start_of_the_shift__c ),12,2)) <5 ),
(DateTimeValue(TEXT(DATEvalue( End_of_Shift__c ))&" 05:00:00") -
Start_of_the_shift__c )*24,
IF
(and (Value( MID(TEXT( End_of_Shift__c ),12,2)) <5, Value( MID(TEXT(
Start_of_the_shift__c ),12,2)) >=5),( End_of_Shift__c -
DateTimeValue(TEXT(DATEvalue( End_of_Shift__c ))&" 00:00:00"))*24,
IF
(and (Value( MID(TEXT( End_of_Shift__c ),12,2)) >=5, Value( MID(TEXT(
Start_of_the_shift__c ),12,2)) >=5,
Value( MID(TEXT( End_of_Shift__c ),9,2)) > Value( MID(TEXT(
Start_of_the_shift__c ),9,2))),
5, 0
)
)
)
)
the above formula is working if the start time & end time is in the same month however if the end time is in the next month then the formula is returning 0. Expectation: Start time is 11PM Oct 31st and end time is 1:00 PM 1st November then the formula is 0. Ideal expectation is to give 5.
Can you please help me how to fix this.