TimeValue and DATEVALUE("6/24/2020 12:00:00 AM") give #VALUE! error

[Carbonze]

I come infrequently to Excel. Date and Time are frustrations!

My system expects dd/mm/yyyy hh:mm:ss
The data are mm/dd/yyyy hh:mm:ss

I want to do arithmetic with time of day. How to tell excel of the date format??

What now?!
In hope for help!!

 

[Dave Patton]

You did not provide a clear question. A few examples complete with expected results would have been useful.

use Data text to Columns delimited space and date MDY
Custom format D4 [h]:mm:ss

Date conversion.xlsm
	A	B	C	D
1		Data Text to Columns
2	03-31-2019 10:00:02	31-03-19	10:00:02	31-Mar-19 10:00:02
3	04-01-2019 10:00:15	01-04-19 0:00	10:00:15	01-Apr-19 10:00:15
4				24:00:13
5
2a

Cell Formulas
Range	Formula
D2:D3	D2	=B2+C2
D4	D4	=D3-D2

 

[Carbonze]

Dave, many thanks. And point taken.

Yes it was text and not date! Imported from power meter.
M

 

[Dave Patton]

Thanks for the feedback.