SOLVED

Change time to specific time frame

%3CLINGO-SUB%20id%3D%22lingo-sub-1271311%22%20slang%3D%22en-US%22%3EChange%20time%20to%20specific%20time%20frame%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271311%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20want%20to%20add%20some%20condition%20however%20it%20is%20for%20Time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20report%20which%20has%20to%20be%20shown%20in%20hourly%20basis.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20take%20an%20example%20%3A%20-%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20are%20the%20timings%20and%20i%20want%20them%20to%20be%20showing%20on%20hourly%20basis%20just%20as%20if%20time%20is%20between%2001%3A00%20-%2002%3A00%20i%20want%20this%20result%20to%20reflect%20in%20next%20column%20as%26nbsp%3B%2001%3A00%20to%2002%3A00%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22213%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2281%22%3EEST%3C%2FTD%3E%3CTD%20width%3D%22132%22%3EIST%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00%3A30%20TO%2001%3A30%3C%2FTD%3E%3CTD%3E10%3A00%20TO%2011%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%3A30%20TO%2002%3A30%3C%2FTD%3E%3CTD%3E11%3A00%20TO%2012%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3A30%20TO%2003%3A30%3C%2FTD%3E%3CTD%3E12%3A00%20TO%2013%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03%3A30%20TO%2004%3A30%3C%2FTD%3E%3CTD%3E13%3A00%20TO%2014%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04%3A30%20TO%2005%3A30%3C%2FTD%3E%3CTD%3E14%3A00%20TO%2015%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E05%3A30%20TO%2006%3A30%3C%2FTD%3E%3CTD%3E15%3A00%20TO%2016%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E06%3A30%20TO%2007%3A30%3C%2FTD%3E%3CTD%3E16%3A00%20TO%2017%3A00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20what%20formula%20can%20be%20used%20here%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1271311%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271361%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20time%20to%20specific%20time%20frame%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F604413%22%20target%3D%22_blank%22%3E%40Vishal_Shukla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20plugged%20this%20formula%20into%20your%20cell%20E93%20and%20it%20worked.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DTEXT(HOUR(C93)%2C%2200%22)%26amp%3B%22%3A00%20TO%20%22%26amp%3BTEXT(HOUR(C93)%2B1%2C%2200%22)%26amp%3B%22%3A00%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20key%20to%20it%20is%20the%20HOUR%20function.%20That%2C%20along%20with%20the%20need%20to%20turn%20all%20the%20numbers%20into%20text%20and%20format%20them%20appropriately%2C%20and%20then%20concatenate%20the%20text%20strings.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271741%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20time%20to%20specific%20time%20frame%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271741%22%20slang%3D%22en-US%22%3EIt%20was%20Really%20great%20info.%20You%20are%20genius%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by Vishal_Shukla (New 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.

Highlighted
It was Really great info. You are genius