2 problems, complicated copying of data based on a "Number" and dates

Question

The first problem I have is that I have a timestamp in one cell which includes the date and time and I would like to know if there is any automated way to separate them into 2 different columns (Date and time).

The other problem is a bit complicated and pardon me for explaining this issue poorly. I have 2 worksheets to work with one includes the data. I would like to copy the "Activity" in sheet A (the one with data) to sheet B based on the date and the number.

This is my sheet A with the data.

This is sheet B that I need to work with. So as you can see the dates on sheet A is in rows and sheet B in columns. So I would like to input the data of "Activity" based on the "Number" (in this example 20981) and put in the correspondent date. I have been typing the data manually and I have over 8000 rows to go through so please help if there is a more automated way of working.

Answer 1

Date and time:

Excel uses a "floating point" number to store both of these together. The fraction portion telling the time of day, the integer portion telling how many days away you are from a base date.
(See the base date by entering 0 in a cell and format it as Date)

The easiest way to simply display them in separate columns:

• Select the cells which are to show the date,
• hold CTRL and hit 1 to open the "Format Cell" dialog,
• then select Date and choose how to display the date.

Now add or select a column to display the times,

• insert =A1-INT(A1) as a formula, assuming A1 holds the first date,
• then use Format Cell above and select a Time format for this cell,
• copy the cell to the relevant places (i.e. column, fill down).
• Alternative: =TEXT(A1,"HH:MM") and no formatting.

Enter new Date & Time -values, or edit existing ones in "A1"-column... and see the second column update accordingly

To use each of date and time separately in formulas: use TEXT(A1,...) to get a character string representation of either.

For moving from column to row for certain data; look into using TRANSPOSE().
Ref: TRANSPOSE @ MS