export time field to spreadsheet as hh:mm (4 Viewers)

[pekajo]

Hi,
Can anyone tell how to export a time field to spreadsheet as hh:mm only. My short date also includes the date

00/01/1900 08:45:00

Thanks
Peter

 

[tvanstiphout]

I'm guessing you are currently exporting a table, including this time field.
Change that to a query, and for the time field use the Format function:
select Format(myTimeField, "hh:nn") as Time_hhnn, OtherFields from myTable
(note the use of "nn" rather than "mm" which is already taken for Month)

Alternatively you can apply formatting in Excel.

 

[Gasman]

Be aware that Format makes it a string.
Not sure what Excel would then do with that?
You could just as easy set that column to that format, but still be able to do math on it.

 

[Trevor G]

Simple question. What method have you tried? There are plenty of solutions but which one will suit you best could be determined by what you've tried so far.

 

[The_Doc_Man]

Be aware that Format makes it a string.
Not sure what Excel would then do with that?

Excel doesn't give a rat's patootie because using proper type-conversion wrappers, it will interpret anything as anything else. Cells in Excel are about as amorphous as Silly Putty. They are the ultimate in data type "Variant" - one cell at a time.

 

[June7]

Excel might still read the formatted output as a date/time. Forcing number (or date/time since it really is stored as number) to be treated as text requires prefixing with apostrophe.

Why do you need this data in Excel?

 

[Ken Sheridan]

Can anyone tell how to export a time field to spreadsheet as hh:mm only. My short date also includes the date

The DateTime data type in Access is implemented as a 64 bit floating point number, with zero representing 30 December 1899 00:00:00. There is really no such thing as a time value in Access, so if you enter a time without a date the numeric value by which this is implemented will actually represent that time on 30 December 1899. To export it as a time to Excel you should do as Tom has described and export the results of a query in which the Format function is called to return it as "hh:nn".

 

[Ken Sheridan]

Be aware that Format makes it a string.
Not sure what Excel would then do with that?
You could just as easy set that column to that format, but still be able to do math on it.

Excel will still treat the expression as a time, and it will be possible to do time arithmetic on it, as with your example, subject to the same proviso that applies in Access, that the result must be less then 24 hours to be intelligible. Otherwise the result, formatted as hh:nn will be the hours and minutes from the date/time value which results from the summation, e.g.

12:45 18:20 7:05

where the third column is the sum of the first two. In Access the correct value can be returned with a function such as:

Public Function TimeElapsed(ByVal dtmTime As Date, strMinSec As String, _
            Optional ByVal blnShowdays As Boolean = False) As String

    ' Returns a date/time value as a duration
    ' in hours etc or days:hours etc if optional
    ' blnShowDays argument is True.
    ' Time format is determined by strMinSec argument,
    ' e.g. "nn" to show hours:minutes,
    ' "nn:ss" to show hours:minutes:seconds,
    ' "" to show hours only

    ' Call the function, in a query for example, like this:
    ' SELECT EmployeeID,
    ' TimeElapsed(SUM(TimeDurationAsDate(TimeStart, TimeEnd)), "nn") As TotalTime
    ' FROM TimeLog
    ' GROUP BY EmployeeID;    

    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    Dim IsNegative As Boolean    

    ' if duration is a negative value then flag as IsNegative
    ' and convert to a positive value
    If dtmTime < 0 Then
        IsNegative = True
        dtmTime = Abs(dtmTime)
    End If    

    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)

    ' get hours
    strHours = Format(dtmTime, "hh")  

    If blnShowdays Then
        TimeElapsed = lngDays & ":" & strHours & Format(dtmTime, ":" & strMinSec)
    Else
        TimeElapsed = Format((Val(strDays) * 24) + Val(strHours), "#,##0") & _
            Format(dtmTime, ":" & strMinSec)
    End If    

    ' remove trailing colon if necessary
    If Right(TimeElapsed, 1) = ":" Then
        TimeElapsed = Left(TimeElapsed, Len(TimeElapsed) - 1)
    End If    

    ' add minus sign if duration is a negatve value
    If IsNegative Then
        TimeElapsed = "-" & TimeElapsed
    End If    

End Function

? TimeElapsed(#12:45#+#18:20#,"nn")
31:05

Something similar in Excel would have the advantage over Access that the return value of the function could be used directly in further time arithmetic. In Access the return value of a summation of date/time values, which would be an unintelligible date/time value, would need to be passed into the above function, as in the example in the introductory remark lines above.

 

[pekajo]

Thank you all for your help

 

[Ken Sheridan]

Something similar in Excel would have the advantage over Access that the return value of the function could be used directly in further time arithmetic

I should have made it clear that this only applies to times under 24 hours. A value such as 31:05 returned in the example in my last post cannot be recognized directly as a time either in Excel or Access. When undertaking time arithmetic every step in a routine should be undertaken on the original date/time values or aggregates thereof, and only formatted as hh:nn or similar as the final operation, using a function like that I posted.