Forum Discussion

Vishal_Shukla's avatar
Vishal_Shukla
Copper Contributor
Apr 01, 2020
Solved

Change time to specific time frame

Hi, I want to add some condition however it is for Time.

 

I am working on a report which has to be shown in hourly basis.

 

Let's take an example : - 

Below are the timings and i want them to be showing on hourly basis just as if time is between 01:00 - 02:00 i want this result to reflect in next column as  01:00 to 02:00 

 

ESTIST
00:30 TO 01:3010:00 TO 11:00
01:30 TO 02:3011:00 TO 12:00
2:30 TO 03:3012:00 TO 13:00
03:30 TO 04:3013:00 TO 14:00
04:30 TO 05:3014:00 TO 15:00
05:30 TO 06:3015:00 TO 16:00
06:30 TO 07:3016:00 TO 17:00

 

 

Please help what formula can be used here

Formulas and Functions
  • mathetes's avatar
    mathetes
    Apr 01, 2020

    Vishal_Shukla 

     

    I plugged this formula into your cell E93 and it worked.

    =TEXT(HOUR(C93),"00")&":00 TO "&TEXT(HOUR(C93)+1,"00")&":00"

     

    The key to it is the HOUR function. That, along with the need to turn all the numbers into text and format them appropriately, and then concatenate the text strings.

2 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor
    Apr 01, 2020

    Vishal_Shukla 

     

    I plugged this formula into your cell E93 and it worked.

    =TEXT(HOUR(C93),"00")&":00 TO "&TEXT(HOUR(C93)+1,"00")&":00"

     

    The key to it is the HOUR function. That, along with the need to turn all the numbers into text and format them appropriately, and then concatenate the text strings.