It concatenates the pieces using & to build the string. This is the cleanest approach since you could potentially run into values where you would otherwise need to adjust the day count or year, and doing power-of-ten math on times gets messy.Ĭ1 contains the date/time after subtracting 3 hours 45 minutes: =B1-TIME(3,45,0)ĭ1 translates the result back to your format: =TEXT(C1,"yy")&TEXT(INT(C1)-DATE(YEAR(C1),1,0),"00#")&"/"&TEXT(MOD(C1,1),"hhmm") Now your entry is in a form on which Excel can do date/time math. Since Excel stores times as fraction of a day, the hour value is divided by 24 and the minute value is divided by 1440 minutes in a day. This uses text functions to parse the pieces and builds the date from the year, using January 1, then adds the day count (and subtracts 1 since the year can't start on January 0). Here's a solution for you based on the problem as originally defined:Ĭell A1 contains a sample date/time in your format.ī1 translates that to an Excel date/time with this formula: =DATE(LEFT(A1,2),1,1)+MID(A1,3,3)-1+MID($A1,7,2)/24+RIGHT($A1,2)/1440
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |