Apr 01 2020 05:49 AM
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
EST | IST |
00:30 TO 01:30 | 10:00 TO 11:00 |
01:30 TO 02:30 | 11:00 TO 12:00 |
2:30 TO 03:30 | 12:00 TO 13:00 |
03:30 TO 04:30 | 13:00 TO 14:00 |
04:30 TO 05:30 | 14:00 TO 15:00 |
05:30 TO 06:30 | 15:00 TO 16:00 |
06:30 TO 07:30 | 16:00 TO 17:00 |
Please help what formula can be used here
Apr 01 2020 06:06 AM
Solution
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.
Apr 01 2020 07:48 AM
Apr 01 2020 06:06 AM
Solution
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.