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.

Resources