Solved

Format date and time - 0001

Posted on 2010-08-16
9
349 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Wilder1626
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33445173
=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")
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33445187
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
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33445197
For that matter, you can use it as a worksheet formula as well.

Wayne
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 11

Author Comment

by:Wilder1626
ID: 33445199
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")
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33445232
s = .Cells(i, 11)
s = Format(DateAdd("n", -1, DateSerial(Left(s, 4), Mid(s, 5, 2), Mid(s, 7, 2)) + TimeSerial(Mid(s, 9, 2), Mid(s, 11, 2), 0)), "yyyymmddhhmm")
.Cells(i, 13) = s
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33445246
webtubbs - would your code handle taking 1 minute off 0000? first day of year?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33445321
Just as an alternative:
=TEXT(TEXT(A1,"0000""-""00""-""00 00"":""00")*1-TIME(0,0,1),"yyyymmddhhmm")
0
 
LVL 81

Expert Comment

by:byundt
ID: 33450364
You can shorten Rory's formula using the backslash to indicate literals:
=TEXT(TEXT(A1,"0000\-00\-00 00\:00")-1/1440,"yyyymmddhhmm")
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 33451243
Thanks to everybody.

Now it works.

Thanks again :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to line 23 55
Clear a Text Box 7 27
Overwrite existing values in a Master file when master file is updated or opened 8 17
Countdown Timer Advanced 5 20
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question