CustNo t1 t2 t3 t4 t_date t_time

35995 0 0 0 300 2008152 4159
35995 0 0 0 303 2008152 4159
35995 20 0 120 226 2008152 8849
35995 0 0 0 0 2008152 9080
35995 1 0 2 5 2008152 9075
35995 0 0 0 0 2008152 9081
35995 0 0 0 232 2008152 8849

These are transactions from transaction log generated by the
application. The t_date is in "julian date" format and i would like to
convert into "MM/DD/YY format" and combine t_date & t_time into one
col.

Also, t1,t2,t3,t4 col are filled in a 24 hr frame so would like these
to be sum per date. How do I do all these in one macro.

Use this UDF function

call with

=ConvertDate(F1, G1), where F1 is the Julian Date and G1 is the time.

Format the results in the worksheet using any Date/Time format


Function ConvertDate(MyDay, MyTime)

'Get 4 digit year
MyYear = Left(MyDay, 4)
'Get Number - Day of the Year
MyDays = Mid(MyDay, 5)
'number of days from Jan 1 of the year specified
StartDate = DateSerial(MyYear, 1, 1)
ConvertDate = StartDate + MyDays - 1
'Time is a decimal number without decimal point
'so add period in front of time
ConvertDate = ConvertDate + ("." & MyTime)

End Function


"aquaflow" wrote:

CustNo t1 t2 t3 t4 t_date t_time

35995 0 0 0 300 2008152 4159
35995 0 0 0 303 2008152 4159
35995 20 0 120 226 2008152 8849
35995 0 0 0 0 2008152 9080
35995 1 0 2 5 2008152 9075
35995 0 0 0 0 2008152 9081
35995 0 0 0 232 2008152 8849

These are transactions from transaction log generated by the
application. The t_date is in "julian date" format and i would like to
convert into "MM/DD/YY format" and combine t_date & t_time into one
col.

Also, t1,t2,t3,t4 col are filled in a 24 hr frame so would like these
to be sum per date. How do I do all these in one macro.