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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

20:41​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

4:16​

[/td][td]

J502​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

5:46​

[/td][td]

1504​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

5:47​

[/td][td]

1105​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

6:01​

[/td][td]

1K02​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

6:17​

[/td][td]

T107​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

6:19​

[/td][td]

1506​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td][/td][/tr][/table]

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.