Custom Date Conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-2587963%22%20slang%3D%22en-US%22%3ECustom%20Date%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2587963%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20issues%20converting%20a%20custom%20date%2Ftime%20to%20another%20format.%20The%20original%20column%20consists%20of%20a%20time%20set%20as%20follows%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22133%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22133%22%3E7%2F9%2F2019%206%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F9%2F2019%206%3A15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F9%2F2019%206%3A30%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F9%2F2019%206%3A45%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F9%2F2019%207%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F9%2F2019%207%3A15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F9%2F2019%207%3A30%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E7%2F9%2F2019%207%3A45%20...%20continued%20to%2012%2F31%2F19%2023%3A45%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20am%20trying%20to%20automatically%20change%20it%20to%20hourly%20for%20the%20entire%20year.%20Basically%20changing%2015%20min%20to%20hourly%20for%20an%20annual%20time%20set.%20The%20dates%20mess%20up%20when%20dragging%20the%20set%20dates%20down%20the%20column.%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22113px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22112px%22%3E7%2F9%2F2019%206%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22112px%22%3E7%2F9%2F2019%207%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22112px%22%3E7%2F9%2F2019%208%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22112px%22%3E7%2F9%2F2019%209%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22112px%22%3E7%2F9%2F2019%2010%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22112px%22%3E7%2F9%2F2019%2011%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22112px%22%3E7%2F9%2F2019%2012%3A00%20...%20continued%20to%2012%2F31%2F2019%2023%3A00%26nbsp%3B%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%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2587963%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2588178%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Date%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2588178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1112952%22%20target%3D%22_blank%22%3E%40GFNar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20not%20clear%20you'd%20like%20to%20convert%20first%20column%20to%20another%20or%20create%20new%20column%20with%20datetime.%20If%20the%20latest%20you%20may%20enter%20first%20datetime%20into%20the%20cell%2C%20in%20one%20below%20add%20to%20it%201%2F24%20and%20drag%20down.%3C%2FP%3E%0A%3CP%3EIf%20first%2C%20what%20is%20the%20logic%20of%20transformation%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello, 

 

I am having issues converting a custom date/time to another format. The original column consists of a time set as follows: 

7/9/2019 6:00
7/9/2019 6:15
7/9/2019 6:30
7/9/2019 6:45
7/9/2019 7:00
7/9/2019 7:15
7/9/2019 7:30

7/9/2019 7:45 ... continued to 12/31/19 23:45

 

But I am trying to automatically change it to hourly for the entire year. Basically changing 15 min to hourly for an annual time set. The dates mess up when dragging the set dates down the column. 

7/9/2019 6:00
7/9/2019 7:00
7/9/2019 8:00
7/9/2019 9:00
7/9/2019 10:00
7/9/2019 11:00
7/9/2019 12:00 ... continued to 12/31/2019 23:00 

 

 

3 Replies

@GFNar 

It's not clear you'd like to convert first column to another or create new column with datetime. If the latest you may enter first datetime into the cell, in one below add to it 1/24 and drag down.

If first, what is the logic of transformation?

@Sergei Baklan 

 

If created in a new column, you can try to use formula: =TEXT(MONTH(A4)&"/"&DAY(A4)&"/"&YEAR(A4)&" "&HOUR(A4)&":00","dd/mm/yyyy h:mm")+0

@Liono1968 

If you drag this formula down it will be 4 cells with 6:00 time, 4 with 7:00, etc. But it shall be one cell for each hour.