Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Convert time

0

I am looking to convert Date and time in excel.

Column H (Age) = Age in Calendar Days (Decimal form)

Column I (Create Node) = Date Created

For example in cell I2 "

2018-06-12-09.20.37.391580-T.01"

I know that the first 8 numerical digits are the create date "2018-06-12". I also believe that the next 6 digits that come after are the Hours "20.47.39" and posibly the rest is just specific to the seconds but not that important. 

Is there and easy way to convert Everything in column I from the current date and time excluding weekends and holidays? Enter in Column M.

Answer
Discuss

Answers

0
Selected Answer

The formula below will convert the text string in your column I to a Date/Time number (of Double format) of equal value, including fractions of seconds.

=DATE(LEFT($I2,4),MID($I2,6,2),MID($I2,9,2))+TIME(MID($I2,12,2),MID($I2,15,2),MID($I2,18,8))

Unfortunately, I don't understand "convert Everything from the current date". You seem to have some kind of calculation in mind, perhaps involving column H. Of course, "Age" should be the time lapsed from the creation date till Now(). Below is a variation of the above formula which does the same calculation but excluding weekends. It returns a number similar to what you have in column H.

=NETWORKDAYS(DATE(LEFT($I2,4),MID($I2,6,2),MID($I2,9,2))+TIME(MID($I2,12,2),MID($I2,15,2),MID($I2,18,8)),NOW())

Holidays would have to be set up. Please ask a separate question about that if needed. Add the name of the range where you define holidays (or its address) at the end of the aboe formula, like ...NOW(),Holidays)

Discuss

Discussion

Thank you so much. Yeah, the holidays I will just make a table to pull from, that's no issue.

Is there any way to pull just the decimal points out of the Column H Age into the networks formula to say 2.7111296 days for example instead of just the flat 2 that I'm getting. 
Sroncey21 (rep: 66) Jan 27, '19 at 2:20 pm
The decimals are all there but they are truncated by your cell format. Set the number of decimals you want in the NumberFormat.
Variatus (rep: 4889) Jan 27, '19 at 7:20 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login