I am storing two dates and times as a text value ie.

"24/12/2009 23:54"
&
"25/12/2009 00:23"

I need to evaluate the subtraction of these two values that are stored
as text ( I need to secure they do not revert to a mm/dd/yyyy format
(which they do despite my many efforts) when I sort) .... anyway....
I need the value as per example above to give me the result in hh:mm
format....

00:29
(29 mins)

I need to do the calculations in VBA

I have tried using DateValue and TimeValue in various arrangements but
can't seam to find the magic words....
do I need to split date and time and evaluate or can I use the
combined date and time in a subtraction formula?
Any help appreciated just to set me on the right path.

Hi Snowfire,

Assuming your values are in A1 & A2 on the active sheet, try:
Sub Demo()
With ActiveSheet
MsgBox Format(CDate(.Range("A1").Value) - CDate(.Range("A2").Value), "HH:mm")
End With
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]


"Snowfire" wrote in message
...
I am storing two dates and times as a text value ie.

"24/12/2009 23:54"
&
"25/12/2009 00:23"

I need to evaluate the subtraction of these two values that are stored
as text ( I need to secure they do not revert to a mm/dd/yyyy format
(which they do despite my many efforts) when I sort) .... anyway....
I need the value as per example above to give me the result in hh:mm
format....

00:29
(29 mins)

I need to do the calculations in VBA

I have tried using DateValue and TimeValue in various arrangements but
can't seam to find the magic words....
do I need to split date and time and evaluate or can I use the
combined date and time in a subtraction formula?
Any help appreciated just to set me on the right path.

Datevalue and day/month/year only and will trim time part but TimeValue will
only do the Hour/Minute/Second part. However, combinding them will help:

format((DateValue(#25/12/2009 00:23#) + Timevalue(#25/12/2009 00:23#)) -
(DateValue(#24/12/2009 23:54#) + Timevalue(#24/12/2009 23:54#)) ,"hh:nn")

it return 00:29


FYI
in case you want to retrieve any part, use datepart, which can retrieve
year/month/day/time/second/weekday...

and
TimeSerial work like DateSerial which you can combined those elements into a
time or date or add them up into a date with time value.





"Snowfire" wrote:

I am storing two dates and times as a text value ie.

"24/12/2009 23:54"
&
"25/12/2009 00:23"

I need to evaluate the subtraction of these two values that are stored
as text ( I need to secure they do not revert to a mm/dd/yyyy format
(which they do despite my many efforts) when I sort) .... anyway....
I need the value as per example above to give me the result in hh:mm
format....

00:29
(29 mins)

I need to do the calculations in VBA

I have tried using DateValue and TimeValue in various arrangements but
can't seam to find the magic words....
do I need to split date and time and evaluate or can I use the
combined date and time in a subtraction formula?
Any help appreciated just to set me on the right path.



.