×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help with Reformat of Date and Time? Please!!

Help with Reformat of Date and Time? Please!!

Help with Reformat of Date and Time? Please!!

(OP)
Driving me crazy! What am I missing?

FileName: Whatever1_Whatever2_Whatever3_20180716143004
Initial Value: 20180716143004
Expected Date Result: 07/16/2018 using varDOS
Expected Time Result: 14:30:04 using varTOS

Here's what I got below. Basically trying to take that long value, and get the date and time in the above format and they'll store to two different fields.

Function GetDOS(text)
GetDOS = Field (text,"_",4)
End Function

Function ReformatDOS(YMD)
ReformatDOS = Mid(YMD,1,2) & "/" & Mid(YMD,3,2) & "/" & Mid(YMD,5,4)
End Function

Function GetTOS(text)
GetTOS = Field (text,"_",4)
End Function

Function ReformatTOS(YMD)
ReformatTOS = Mid(text,1,2) & ":" & Mid(test,3,2) & ":" & Mid(YMD,5,4)
End Function

-----Separate Application that is calling the VBScripts above; Should be good as I'm just getting the value from VB and store it as an index field in an app.------------

//Get Date of Service from FileName
SET varDOS SCRIPT GetDOS varFileName
SET varDOSF SCRIPT ReformatDOS varDOS
STORE varDOSF "DATE_OF_SERVICE"
//Get Time of Service from FileName
SET varTOS SCRIPT GetTOS varFileName
SET varTOSF SCRIPT GetTOSF varTOS
STORE varTOSF "TIME_OF_SERVICE"

RE: Help with Reformat of Date and Time? Please!!

Hi,

GetDOS and GetTOS are returning identical results and likwise ReformatDOS and ReformatTOS return identical results: You’ll get a DATE from both.

You don’t need GetTOS. Your ReformatTOS just needs different arg1 values in the MID() function.

The second thing is that a Date is not a string value. Use CDate()

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Help with Reformat of Date and Time? Please!!

(OP)
Skip,
Dang it...yeah, not supposed to be that way. So, I need the TOS to be (time). I'll get that changed, duh! But, I believe the key here is that I would need to parse out the relevant digits right? So, from 20180716143004...I need to parse the first 8 digits for the date and then get that value reformated and the last 6 digits (or starting at the 9th digit) for the time. Is that what I'm missing is some type of TRIM?

RE: Help with Reformat of Date and Time? Please!!

(OP)
So, I actually updated as the below aside from using time cause I got what you meant that I should change the arg.

Function GetDOS(text)
GetDOS = Field (text,"_",4)
End Function

Function ReformatDOS(YMD)
ReformatDOS = Mid(YMD,1,2) & "/" & Mid(YMD,3,2) & "/" & Mid(YMD,5,4)
End Function

Function GetTOS(text)
GetTOS = Field (text,"_",4)
End Function

Function ReformatTOS(text)
ReformatTOS = Mid(text,1,2) & ":" & Mid(text,3,2) & ":" & Mid(text,5,4)
End Function

RE: Help with Reformat of Date and Time? Please!!


CODE

'
    dat = Mid(text, 1, 4) & "/" & Mid(text, 5, 2) & "/" & Mid(text, 7, 2)
    dat = dat & " " & Mid(text, 9, 2) & ":" & Mid(text, 11, 2) & ":" & Mid(text, 13, 2)
    YourDateTime = CDate(dat) 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Help with Reformat of Date and Time? Please!!

(OP)
I'm assuming I would use the above and just store then to 2 different variables since they are being indexed into 2 different fields (like excel)?

date = dat
time = Mid(text,9,2) & .........
???

20180716143004 should be 07/16/2018 as 1 value and 14:30:04 as the other value.

RE: Help with Reformat of Date and Time? Please!!

Sure.

But you’ve got to CONVERT your Strings to Date/Time.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Help with Reformat of Date and Time? Please!!

(OP)
Function GetDOS(text)
GetDOS = Field (text,"_",4)
GetDOS = CDate(GetDOS)
End Function

I feel like I'm missing the mark.

RE: Help with Reformat of Date and Time? Please!!

CODE

Function GetDOS(text)
GetDOS = Field (text,"_",4)
GetDOS = Mid(text, 1, 4) & "/" & Mid(text, 5, 2) & "/" & Mid(text, 7, 2)
GetDOS = CDate(GetDOS)
End Function

Function GetTOS(text)
GetTOS = Field (text,"_",4)
GetTOS = Mid(text, 9, 2) & ":" & Mid(text, 11, 2) & ":" & Mid(text, 13, 2)
GetTOS = CDate(GetTOS)
End Function 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Help with Reformat of Date and Time? Please!!

(OP)
Oh you're freaking amazing!!! Wow...like I had the right idea. Much appreciated.

RE: Help with Reformat of Date and Time? Please!!

(OP)
Skip, unfortunately, above is pulling the current system date and time. It's not taking the value, parsing, and reformatting. Any other ideas?

String: 20170716134008

I tried....

Function GetDOS(theDate)
theDate = Field (text,"_",4)
theDate = Mid(theDate,5,2) & "/" & Mid(theDate,7,2) & "/" & Mid(theDate,1,4)
GetDOS = theDate
'GetDOS = CDate(GetDOS)
End Function

---separate script, not VBScript, calling the VBScript above--
//Get Date of Service from FileName
SET varDOS SCRIPT GetDOS varFileName
STORE varDOS "DATE_OF_SERVICE"

So, shouldn't this have the variable varDOS go to VBScript GetDOS and pull 20170716 and then 07 16 2017 and place / in between to make 07/16/2017. Then come back to non VBScript and store 07/16/2017 as the value?
?? I referenced GetDOS as theDate so that in another program I'm calling GetDOS as the value to input?

RE: Help with Reformat of Date and Time? Please!!

CODE

text = "Whatever1_Whatever2_Whatever3_20180716143004"

Function GetDOS(text)
text = Field (text,"_",4)
text = Mid(text, 1, 4) & "/" & Mid(text, 5, 2) & "/" & Mid(text, 7, 2)
GetDOS = CDate(text)
End Function

text = "Whatever1_Whatever2_Whatever3_20180716143004"

Function GetTOS(text)
text = Field (text,"_",4)
text = Mid(text, 9, 2) & ":" & Mid(text, 11, 2) & ":" & Mid(text, 13, 2)
GetTOS = CDate(text)
End Function 

Sorry for trying to do too much in too little time.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Help with Reformat of Date and Time? Please!!

BTW, have not seen your “comment” from the link I posted last night.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Help with Reformat of Date and Time? Please!!

(OP)
Skip!!!!! In my world "text" = "varFileName" from my other XDS script. Dude...freaking right on! I totally appreciate it! So working right now! Basically, I replaced text with varFileName 'cause I turned my screen sideways and I finally picked up what you were putting down!

I clicked on the link and it said it was an error.

RE: Help with Reformat of Date and Time? Please!!

I must have been sleeping or I’m getting senile (not viewing a river in Egypt)

Click on this FAQ link, scroll down, click on Send a comment to SkipVought link. This will send an eMail to me with your eMail address. In turn, I’ll send you an eMail and we’ll go from there. I don’t IM.
FAQ707-4594: How to use the Watch Window as a Power Programming Tool

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close