新聞財經娛樂♬資訊 News Economy

Thanks for your score and
please let us know in advance  you want the code in VBA code  instead of Excell cell formula
Be reminded, next time, also including VBA for your next thread tag so that
other EE expert can help you in much faster way

Thank yu all excel experts,
I accept the first answer that works.  There are so many great information from

Formula modification for final version, please review,

Please see attached download file from re-sending update of  "data.xls"  for  final xls file

*******A*******************B***********C*************
1
2 7/14/2012 20:00:00
3  7/14/2012 21:00:00
4  7/14/2012 22:00:00
5  7/14/2012 23:00:00
6  7/15/2012 00:00:00
7  7/15/2012 01:00:00
8  7/15/2012 02:00:00

Put B2  formula as
=IF(ISERROR(MID(RIGHT(B1,8),1,2)*1)=FALSE,IF(B1>=23,B1+1,MID(RIGHT(A2,8),1,2)*1),MID(RIGHT(A2,8),1,2)*1)


Put C2 formula as

=IF(B2>23,MID(C1,1,10),MID(A2,1,LEN(A2)-8)) & " " & B2 & RIGHT(A2,6)




And the result will come out
*******A*******************B***********C*************
1
2 7/14/2012 20:00:00      20.00       7/14/2012  20:00:00
3 7/14/2012 21:00:00      21.00       7/14/2012  21:00:00
4 7/14/2012 22:00:00      22.00       7/14/2012  22:00:00
5 7/14/2012 23:00:00      23.00       7/14/2012  23:00:00
6 7/15/2012 00:00:00      24.00       7/14/2012  24:00:00
7 7/15/2012 01:00:00      25.00       7/14/2012  25:00:00
8 7/15/2012 02:00:00      26.00       7/14/2012  26:00:00


And if you need, please hidden B colume since it is just temporarily used

That is all, otherwise, you need VBA module if you don't like column B for temporary varible


It works completely and final


Duncan

AFAIK, you cannot format a date time serial number so it displays both the date and a time greater than 24 hours. As a workaround, you can convert the date time serial number to text using the formula below, but that may defeat your purpose.
="7/14/2012 " & TEXT(A1-"7/14/2012","[hh]:mm:ss")

Could you please describe the underlying problem you are trying to solve? There is likely a different approach to solve it than the one you request.

could you simply sort it after it comes out?

    ThisWorkbook.Worksheets("mySheet").Sort.SortFields.Clear
    ThisWorksheets("mySheet").Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("mySheet").Sort
        .SetRange Range("A:A")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With