how to handle tdate and time

[Snowfire]

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.

 

[macropod]

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

 

[Leung]

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]

Thank you both for your replies they both work for a 24 Hr. period....
pushing my luck her.... but how could the code be adapted to allow
for calculating Hours and minutes over a longer (several days) span
between dates and time.

ie.
25/12/2009 00:23 - 20/12/2009 23:54 for example?

would I need to evaluate the days in between and ...say multiply by 24
and add this to the hours/minutes calculated from your code? (needless
to say) I have tried it but it refuses to amalgamate.

 

[macropod]

Hi snowfire,

To include 'days' in the output it's simple: change "HH:mm" in my code to "dd hh:mm"

To express the days as hours, one way is:
Sub Demo()
Dim Mins, Hrs, Days As Integer
With ActiveSheet
Days = Format(CDate(.Range("A1").Value) - CDate(.Range("A2").Value), "dd")
Hrs = Format(CDate(.Range("A1").Value) - CDate(.Range("A2").Value), "hh")
Mins = Format(CDate(.Range("A1").Value) - CDate(.Range("A2").Value), "mm")
MsgBox Days * 24 + Hrs & ":" & Mins
End With
End Sub