You need to sign in to do that
Don't have an account?
mitsvik
i have been asked to create a report for a 24 hour time interval from midnight until 24:00 (See legacy). This is for a chat request that comes in for that period. The request time is captured and used this formula to get the 15 min time interval. See However, i am also asked to show the times when no request is made and corresponding values as 0. I am not sure how i should go about getting this inserted in the time interval. Should i create another field that details the 30 min time interval. If so how do i go about doing it.
LPAD(
TEXT( VALUE( MID(TEXT(RequestTime - 0.2085),12,2) )
+ IF( VALUE(MID(TEXT(RequestTime - 0.2085 ),15,2)) > 53, 1, 0)
), 2, "0")
+ IF( VALUE(MID(TEXT(RequestTime - 0.2085),15,2)) < 8, ":00" ,
IF( VALUE(MID(TEXT(RequestTime - 0.2085 ),15,2)) < 24, ":15" ,
IF( VALUE(MID(TEXT( RequestTime - 0.2085 ),15,2)) < 38, ":30" ,
IF( VALUE(MID(TEXT( RequestTime - 0.2085 ),15,2)) < 54, ":45" ,
":00")
)))
24 hour time interval
i have been asked to create a report for a 24 hour time interval from midnight until 24:00 (See legacy). This is for a chat request that comes in for that period. The request time is captured and used this formula to get the 15 min time interval. See However, i am also asked to show the times when no request is made and corresponding values as 0. I am not sure how i should go about getting this inserted in the time interval. Should i create another field that details the 30 min time interval. If so how do i go about doing it.
LPAD(
TEXT( VALUE( MID(TEXT(RequestTime - 0.2085),12,2) )
+ IF( VALUE(MID(TEXT(RequestTime - 0.2085 ),15,2)) > 53, 1, 0)
), 2, "0")
+ IF( VALUE(MID(TEXT(RequestTime - 0.2085),15,2)) < 8, ":00" ,
IF( VALUE(MID(TEXT(RequestTime - 0.2085 ),15,2)) < 24, ":15" ,
IF( VALUE(MID(TEXT( RequestTime - 0.2085 ),15,2)) < 38, ":30" ,
IF( VALUE(MID(TEXT( RequestTime - 0.2085 ),15,2)) < 54, ":45" ,
":00")
)))
add this code also:
IF( VALUE(MID(TEXT( RequestTime - 0.2085 ),15,2)) < 8, ":45" ,
If you find your Solution then mark this as the best answer.
Thank you!
Regards,
Suraj Tripathi