Modify Date and Time

Hi everyone. I really need some help with modifying stuff in excel

Here's the situation: (please refer to attached picture)

- Column B is a column for a Date. The format is YearMonthDay. For example, if I have 20000105 , the first 4 digits is the Year, the 2 digits after that is the Month and the last 2 digits is the Day. So that would be January 5, 2000. I need to change 20000105 to this format: 1/5/2000 or to some other regular date format so that sorting can be done by date. This has to be done for all cells in Column B.

- Column C is a column for Hour(or Time) . The format is HourMinuteSeconds . The hour is military time. For example, if I have 150018 , the first 2 digits is Hour, the next 2 digits is Minute and the last 2 digits is Seconds. So that would be 3:00:18 or Three o'clock 18 seconds. I need to change 150018 to this format: 3:00:18 or some other regular time format so that sorting can be done by time. This has to be done for all cells in Column C.

Note that time can also appear as something like 94936 wherein only the first digit is the hour. So this would be 9:49:36 or Nine forty nine and 36 seconds.

If anyone can suggest a macro for this it would be much appreciated. Thank you.

First Of All I recommend you create a copy of worksheet as for backup.

Luckily that data entry in column B is Number and enter in 8 digits. So please go to the blank cell use this formula to convert data in column B into date. for example input formula in cell F2

=date(trunc(b2/10000), month(value(mid(b2,5,2)),value(right(b,2)))

Then Copy paste special in to F2
DATE is function to convert real number into date serial number

The Date syntax is:

Please Login or Register  to view this content.

TRUNC is the function to round the number in to the number in the digit nth.

The TRUNC syntax is

Please Login or Register  to view this content.

Digit is the decimal place no. If you omitted is mean that you want to round number into Integer

RIGHT is the function to select the character from text in specific number character

The RIGHT syntax is

Please Login or Register  to view this content.

MID is the function to extract some character from text, using the specific position

The MID syntax is

Please Login or Register  to view this content.

from you question please use this formula

=TIME(trunc(C2/10000),(MOD(C2,10000)-MOD(C2,100))/100,MOD(C2,100))

MOD is function calculate the remain from divided

Hope it will Help full information to you

Sincerely

VALUE is the function to convert number store as text to store as real value.

The VALUE syntax is

=VALUE(number)

To convert number in time Using TIME Function

Time syntax is

=TIME(HOUR,MINUTE,SECOUND)

After convet data using formula. Please copy the paste special value in the same cells.