Change Text Time Date to todays Date

[tazeo]

I have a list of times which were bought over as Text, so the date is 01/01/1900.

I want to update the date to todays date.

Tried this: =DAY(H2)+MONTH(H2)+YEAR(H2)+HOUR(A9)+MINUTE(A9)+SECOND(A9) which gives me a 1905 year date
As well as =DAY(H2)&MONTH(H2)&YEAR(H2)&HOUR(A9)&MINUTE(A9)&SECOND(A9) which gives me 29920186470 which is getting close. i.e. 29/9/2018 6:47:00

I think I am close to the answer but not sure how to take the next step to get the result I want. Of course I could be totally wrong on this as well!!!

 

[mole999]

would =now() work

 

[tazeo]

Excel 2016 (Windows) 64 bit

	A​	B​
1​	20:41​	​
2​	4:16​	J502​
3​	5:46​	1504​
4​	5:47​	1105​
5​	6:01​	1K02​
6​	6:17​	T107​
7​	6:19​	1506​

Ok on review not very clear question/problem. Sorry.

So A1 has =NOW()
A2:A7 has the times entered as text. For example where in A2 is 4:16 - it is actually 0.177777777777778 which is 0/01/1900 4:16:00 AM

My ultimate goal is to use the NOW function to show the current time, and with conditional formating show which time in A2:A* I am upto. So I need a way to amend the times in A2:A* so that when compared to the NOW function I get real results.

Or in other words I need a way to change 01/01/1900 4:16:00 AM to 29/09/2018 4:16:00 AM or which every day I am looking at.

 

[jtakw]

Hi,

Would this help:

Book1
	A	B
1	9/29/2018 11:42
2	4:16	9/29/2018 4:16
3	5:46	9/29/2018 5:46
4	5:47	9/29/2018 5:47
5	6:01	9/29/2018 6:01
6	6:17	9/29/2018 6:17
7	6:19	9/29/2018 6:19
Sheet300

Cell Formulas
Range	Formula
A1	=NOW()
B2	=INT(A$1)+A2

B2 formula copied down.

 

[mole999]

based on that would =INT(TODAY())+A1 be better, formatted as dd/mm/yyyy hh:mm

 

[MARK858]

@mole999, if you are using =TODAY() then you don't need the INT as it doesn't have a time portion (well strictly speaking it has 00:00 as the time part).

Excel 2010
	A	B
2	04:16	29/09/2018 04:16
3	05:46	29/09/2018 05:46
4	05:47	29/09/2018 05:47
5	06:01	29/09/2018 06:01
6	06:17	29/09/2018 06:17
7	06:19	29/09/2018 06:19
Sheet2

Cell Formulas
Range	Formula
B2	=TODAY()+A2

You will probably find that it causes more calculation than the INT(A1) as you are evaluating TODAY() in each cell rather than once.

 

[tazeo]

Wow, just goes to show how a brain can get caught up doing something tricky and keep heading down that path. A very elegant solution - Much obliged to you all.

 

[jtakw]

I'm sure we're All happy you got it working for you, you're welcome from all of us.