Excel Function to Convert F91300 SCHSTTIME in JDE to DD/MM/YY HH:MM:SS Format

F91300 is the Schedule Job Master table which stores all the details about the jobs scheduled on the JDE’s default scheduler. Report name, version, start time, recurrence, execution server and job queue are some of important columns of this table. Now most of the information in F91300 table can be understood easily EXCEPT “Start Time”.

Start Time is a field which displays a numerical value in the databrowser. Actually this value is internally used by JDE to calculate the “Start Date” and “Start Time” which can be observed in P91300 application under “Job Revisions” for a particular job. Thus, even if we take an extract from Schedule Job Master (F91300) table, we can only find a “number” as start time. My objective in this tutorial is to provide you a function which can be easily intergrated into your Microsoft Excel software so as to convert this “number” into “Start Date and Start Time”. I have named this function as “ST2DNT” which stands for “Start Time to Date and Time”.

Let us see how this function works :

• I take an extract from F91300 table for a particular set of jobs.
• All I can see under the “SJSCHSTTIME” column is some numbers which I cannot decode :
  
• Inserting a new column besides this column and using my magical function to obtain the start date and time which I can understand :
  
• Voila! I have acheived the desired result.
• Note : Depending upon your JDE’s DST settings, the output obtained may need a couple of hours correction. This can be obtained by passing an additional parameter which marks the “no. of minutes” to add as follows :
  
  Observe how the time has increased by 2 hours (i.e. 120 min) by passing 120 as the second parameter.

Enough said, let’s get it integrated in your Excel as well 🙂

Go through the following steps to integrate ST2DNT function in your Excel.

1. Open an Excel Worksheet.
2. Press Alt+F11 to open Visual Basic Editor.
3. Press Ctrl+G to open Immediate Window.
4. Type ? application.StartupPath and Press Enter :
   
   This will provide you with the path to “XLStart” Folder. We need this to save the add-in file we are going to create. By saving the add-in file in this folder, the functions we will be creating can be used in all excel sheets and not just the one we define the functions in.
5. Now, click on “Insert” > “Module”
6. Paste the following code in the code window

   Function ST2DNT(schsttime As Long, Optional mindiff As Integer = 0)
   'Author : Nimish Prabhu
   'Website : http://www.nimishprabhu.com
   Dim startdate, starttime
   startdate = DateAdd("n", schsttime + mindiff, "01-Jan-1970")
   ST2DNT = Format(startdate, "dd/MM/yyyy hh:mm:ss")
   End Function
   

   Note : If you want the output as MM/DD/YYYY instead of DD/MM/YYYY, replace the following line :

   ST2DNT = Format(startdate, "dd/MM/yyyy hh:mm:ss")

   with

   ST2DNT = Format(startdate, "MM/dd/yyyy hh:mm:ss")

   Note that the capital MM are intentional, do not replace them with mm, else you will get minutes in your output instead of month.

   

   Make sure the code you paste in the window, gets pasted properly as shown above and not in a single line.

7. Now Save the workbook as “Add-in” (File > Save Workbook)
   Please do not forget to save the workbook in XLStart directory. Else the code won’t work in other workbooks.
   
8. That’s it. Now use the formula in any workbook.
   

Hope this tutorial was helpful. Comment below in case of queries.