asked on

Format date and time - 0001

Hello all,

In my excel .Cells(i, 11), i have a date and time format "201008160100" for example. Year:2010, month:08, Day:10, Hour:01, Min:00.

I need to have in cell 13 the value from .Cells(i, 11) but remove 1 minute.

The result of the example "201008160100" would be "201008160059"

Thanks for your help

.Cells(i, 13) = .Cells(i, 11) - "0001"

Open in new window

cyberkiwi

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),0)-TIME(0,1,0),"yyyymmddhhmm")

Wayne Taylor (webtubbs)

Try this function....

Function subtractMinute(dateString As String) As String
Dim timeString As String
timeString = Right(dateString, 4)

Dim timeVal As Date
timeVal = TimeSerial(Left(timeString, 2), Right(timeString, 2), 0)

Dim newDateString As String
newDateString = Left(dateString, 8) & Format(DateAdd("n", -1, timeVal), "hhmm")

subtractMinute = newDateString
End Function

Use it like this....

MsgBox subtractMinute("201008160100")

Wayne

Wayne Taylor (webtubbs)

For that matter, you can use it as a worksheet formula as well.

Wayne

Wilder1626

ASKER

Hello,

I have a syntax error:
.Cells(i, 13) = TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),0)-TIME(0,1,0),"yyyymmddhhmm")

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

webtubbs - would your code handle taking 1 minute off 0000? first day of year?