SOLVED

Change time to specific time frame

Copper Contributor

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

2 Replies
best response confirmed by Vishal_Shukla (Copper Contributor)
Solution

@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.

It was Really great info. You are genius
1 best response

Accepted Solutions
best response confirmed by Vishal_Shukla (Copper Contributor)
Solution

@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.

View solution in original post