Excel Macro : How can I get the timestamp in "yyyy-MM-dd hh:mm:ss" format?

excel vba

333,745

Solution 1

Try with: format(now(), "yyyy-MM-dd hh:mm:ss")

Solution 2

DateTime.Now returns a value of data type Date. Date variables display dates according to the short date format and time format set on your computer.

They may be formatted as a string for display in any valid date format by the Format function as mentioned in aother answers

Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")

Solution 3

Format(Now(), "yyyy-MM-dd hh:mm:ss")

Solution 4

If some users of the code have different language settings format might not work. Thus I use the following code that gives the time stamp in format "yyymmdd hhMMss" regardless of language.

Function TimeStamp()
Dim iNow
Dim d(1 To 6)
Dim i As Integer


iNow = Now
d(1) = Year(iNow)
d(2) = Month(iNow)
d(3) = Day(iNow)
d(4) = Hour(iNow)
d(5) = Minute(iNow)
d(6) = Second(iNow)

For i = 1 To 6
    If d(i) < 10 Then TimeStamp = TimeStamp & "0"
    TimeStamp = TimeStamp & d(i)
    If i = 3 Then TimeStamp = TimeStamp & " "
Next i

End Function

Solution 5

this worked best for me:

        Cells(partcount + 5, "N").Value = Date + Time
        Cells(partcount + 5, "N").NumberFormat = "mm/dd/yy hh:mm:ss AM/PM"

View more solutions

333,745

Parth Bhatt

I am iPhone and iPad developer. https://github.com/akashraje/BidirectionalCollectionViewLayout ^[-_,A-Za-z0-9]$ NSRegularExpression *reg = [NSRegularExpression regularExpressionWithPattern:@"(#[a-zA-Z0-9_-]+)" options:NSRegularExpressionCaseInsensitive error:nil]; NSString *stringData = @"This is Parth known as #parth and this is an awesome place. I am fan of #scganguly."; int count = [reg numberOfMatchesInString:stringData options:0 range:NSMakeRange(0, [stringData length])]; NSLog(@"%d",count); if(count>0) { NSArray *array = [reg matchesInString:stringData options:0 range:NSMakeRange(0, [stringData length])]; NSLog(@"%@",array); NSMutableArray *stringArray = [[NSMutableArray alloc] init]; for (NSTextCheckingResult *result in array) { NSString *stringFinal = [stringData substringWithRange:result.range]; if(stringFinal != nil) { [stringArray addObject:stringFinal]; } } NSLog(@"stringArray: %@",stringArray); } For @user: @"(@[a-zA-Z0-9_]+)" NSLog(@"Request String: %@", requestString); NSData *requestData = [NSData dataWithBytes: [requestString UTF8String] length: [requestString length]]; // NSString *fileLoc = [[NSBundle mainBundle] pathForResource:@"url" ofType:@"plist" ]; // NSDictionary *fileContents = [[NSDictionary alloc] initWithContentsOfFile:fileLoc]; // NSString *urlLoc = [fileContents objectForKey:@"baseURL"]; NSString *urlLoc = @"http://portal.abc.com/candidate/post-info"; NSLog(@"URL is %@",urlLoc); NSMutableURLRequest *request = [[NSMutableURLRequest alloc] initWithURL: [NSURL URLWithString: urlLoc]]; NSString *postLength = [NSString stringWithFormat:@"%d", [requestData length]]; [request setHTTPMethod: @"POST"]; [request setValue:postLength forHTTPHeaderField:@"Content-Length"]; [request setValue:@"application/json" forHTTPHeaderField:@"Content-Type"]; [request setHTTPBody: requestData]; http://blog.stackoverflow.com/archive/ https://developer.apple.com/library/prerelease/ios/documentation/General/Conceptual/CocoaTouch64BitGuide/Introduction/Introduction.html http://nscookbook.com/2013/03/ios-programming-recipe-19-using-core-motion-to-access-gyro-and-accelerometer/ http://code4app.net/category/coremotion http://www.devx.com/wireless/Article/44799 upload audio file to php ios Android post request on IOS https://developers.facebook.com/docs/ios/share https://github.com/oliverbarreto/FastFavs/blob/master/TODO2.h

Updated on July 05, 2022

Comments

Parth Bhatt almost 2 years

I am using DateTime.Now in my Excel Macro to show the current timestamp.

It shows timestamp in "dd-MM-yyyy hh:mm:ss" format.

Instead, how can I get the timestamp in "yyyy-MM-dd hh:mm:ss" format?
A.Sommerh almost 10 years

Don't work! Date contain only the calendar date but the hour, minutes and seconds are fixed to 0:00:00 ! You have to use Now() instead..