Skip to main content
Topics#Sales CloudQuestion de Lakhan Meghani
Forum Ambassador Lakhan Meghani (Cube84) a posé une question dans #Sales Cloud
7 sept. 2021, 07:20

Formula

#Sales Cloud

I have 2 datetime fields

 

Say

Call Start Time  9/7/2021, 11:00 am

Call End Time    9/7/2021, 11:15 am

 

Formula output i need- If time exists between 11:00am to 11:15am then Output should be "11 - 12am"

Similary if time is from 2pm to 2:15pm then output should be "2 to 3pm"

 

P.S- I do have such ranges from 9am to 7pm

Any suggestions and inputs ?

15 réponses
  1. Forum Ambassador Eric Praud (Activ8 Solar Energies)
    7 sept. 2021, 08:52

    I forgot an &:

    IF( OR(
    CreatedDate< DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),3,14)
    -(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),3,14))-1)) + 17/24,
    CreatedDate>=
    DATETIMEVALUE(DATE(YEAR(DATEVALUE(CreatedDate)),11,7)
    -(WEEKDAY(DATE(YEAR(DATEVALUE(CreatedDate)),11,7))-1))+17/24
    ),
    TEXT(HOUR(TIMEVALUE(CreatedDate-8 /24))-IF(HOUR(TIMEVALUE(CreatedDate-8/24))>23,24,0)-
    IF(HOUR(TIMEVALUE(CreatedDate-8 /24))-IF(HOUR(TIMEVALUE(CreatedDate-8/24))>23,24,0)>12,12,0))
    &
    IF(HOUR(TIMEVALUE(CreatedDate-8 /24))-IF(HOUR(TIMEVALUE(CreatedDate-8/24))>23,24,0)>12," pm to "," am to ")&
    TEXT((HOUR(TIMEVALUE(CreatedDate-8 /24))+1)-IF((HOUR(TIMEVALUE(CreatedDate-8/24))+1)>23,24,0)-
    IF((HOUR(TIMEVALUE(CreatedDate-8 /24))+1)-IF((HOUR(TIMEVALUE(CreatedDate-8/24))+1)>23,24,0)>12,12,0))
    &
    IF((HOUR(TIMEVALUE(CreatedDate-8 /24))+1)-IF((HOUR(TIMEVALUE(CreatedDate-8/24))+1)>23,24,0)>12,"pm","am")
    ,
    TEXT(HOUR(TIMEVALUE(CreatedDate-7/24))-IF(HOUR(TIMEVALUE(CreatedDate-7/24))>23,24,0)-
    IF(HOUR(TIMEVALUE(CreatedDate-7/24))-IF(HOUR(TIMEVALUE(CreatedDate-7/24))>23,24,0)>12,12,0))
    &
    IF(HOUR(TIMEVALUE(CreatedDate-7/24))-IF(HOUR(TIMEVALUE(CreatedDate-7/24))>23,24,0)>12," pm to "," am to ")&
    TEXT((HOUR(TIMEVALUE(CreatedDate-7 /24))+1)-IF((HOUR(TIMEVALUE(CreatedDate-7/24))+1)>23,24,0)-
    IF((HOUR(TIMEVALUE(CreatedDate-7 /24))+1)-IF((HOUR(TIMEVALUE(CreatedDate-7/24))+1)>23,24,0)>12, 12,0))
    &
    IF((HOUR(TIMEVALUE(CreatedDate-7 /24))+1)-IF((HOUR(TIMEVALUE(CreatedDate-7/24))+1)>23,24,0)>12,"pm", "am")
    )
Rédiger une réponse...
0/9000