Break Imported Text Field from Microsoft Excel into Component Parts

[Welshgasman]

For the minutes look for the : with Instr() and Left() and Mid() functions.


For the dates, use the Format() function and Val() for the numeric date value.

Could even create your own function for the minutes process?

[mike60smart]

Hi Tinman

I have applied Steps 2 & 3 in the attached.

Unable to achieve Step 1 though ***Edit After Gasman's tips see the modified Db attached

[Welshgasman]

Hi Tinman

I have applied Steps 2 & 3 in the attached.

Unable to achieve Step 1 though

Well it is even easier.

There are Hour() and Minute() functions, so simple math?

[mike60smart]

Hi Gasman

I always find Access amazing. Have never ever used Hour() or Minute() Functions so yet again another day learning something.

[June7]

There is also a Year() function.

You have used Format property for Time and ShortTime calculated fields. Format property does not change the data. Therefore, when you reference these calculated fields in subsequent expressions, the actual values are used, not the formatted version you see. That's why the MinutesOfDay calculation returns data like "12/2/2022 8"

Format() function does change data.

To achieve proper sort order of string dates, need to include placeholder zeros, such as 2022-04-01. Format() can accomplish that: Format(DT, "yyyy-mm-dd"). And it just makes values appear more uniform.

[TINMAN]

Thank you following your recommendation I was able to create two fields one in the format 2022-01-01 and another in the format 20220101.

Now I just need to break the time into Hour and Minutes so I can calculate total minutes of the day when the employee entrance occurs. I am going to try and use Hour and Minute expression to achieve this result.

Thanks for your help.

[June7]

Exactly what are you trying to calculate with the Hour and Minute functions? If time is 10:00:00am and you pull 10 that is 10 hours from midnight. What purpose does it serve to calculate minutes from midnight? What do you intend to do with this value?

[TINMAN]

What I am trying to do is determine the frequency that an employee is trying to enter at a given minute during the day compared to his assigned entry time in minutes, thus I want to convert the entry time in hours and multiply it times 60 and then add the entry time in minutes after the hour to determine total entry time in minutes.

Unfortunately, I am struggling to determine how to do this.

I thought maybe I could use 24 hrs per day x 60 minutes per hour for 1,440 minutes per day to break the portion of the day into minutes.

[ArviLaanemets]

The easiest way would be calculate real date and time values in Excel, and read those (or combined datevalue) into your Access database. All your Part# values you can get easily from those imported date and time fields (or from datetime filed) formatting the control linked to apropriate field in wanted format. Or you can use either TEXT() or FORMAT() Access functions to return equivalents of your Part# values as strings.

Excel formula examples (with your date string in cell A1):

Date (Probably you have edit the formulas. Almost surely you have comma as parameter delimiter, and also check what is your array delimiter in MATCH() function. I have semicolons for both):
=DATE(MID(A1;SEARCH(",";A1;SEARCH(",";A1;1)+2)+2;4);MATCH(MID(A1;SEARCH(",";A1;1)+2;SEARCH(" ";A1;SEARCH(",";A1;1)+2)-SEARCH(",";A1;1)-2);{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"};0);MID(A1;SEARCH(" ";A1;SEARCH(",";A1;1)+2)+1;SEARCH(",";A1;SEARCH(",";A1;1)+2)-SEARCH(" ";A1;SEARCH(",";A1;1)+2)-1))

Time:
=TIMEVALUE(MID(A1;SEARCH(",";A1;SEARCH(",";A1;1)+2)+7;LEN(A1)-(SEARCH(",";A1;SEARCH(",";A1;1)+2)+7)+1))

To get datetime values, you simply add both date and time formulas (DatetimeResult = DateResult+TimeResult).

[Welshgasman]

Thank you following your recommendation I was able to create two fields one in the format 2022-01-01 and another in the format 20220101.

Now I just need to break the time into Hour and Minutes so I can calculate total minutes of the day when the employee entrance occurs. I am going to try and use Hour and Minute expression to achieve this result.

Thanks for your help.

Make sure the value you use is an actual date type, before using those functions.

[ArviLaanemets]

To break TimeFieldValue value to hours
Hours = Int(TimeFieldValue*24)
To break TimeFieldValue to minutes
a) whole TimeFieldValue (how much minutes from midnight)
Minutes = Int(TimeFieldValue*24*60)
b) remains in minutes from full hours
Minutes = Int((TimeFieldValue*24-Int(TimeFieldValue)*24))*60)

[Micron]

Post 9 has the result of converting to date/time data type and then using date functions to get one of the desired results in a query. Seventeen posts later it seems to be about converting strings to date/time and using Date functions.

[TINMAN]

Just a note to you and all the other people who commented on my original post with recommendations, as a result I have achieved what I set out to do and will I mark the post as solved.

THANKS EVERYONE!!

I am attaching the final sample database.