microsoft excel

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 :
    Scheduler Start Time Numerical
  • Inserting a new column besides this column and using my magical function to obtain the start date and time which I can understand :
    ST2DNT excel function demo
  • 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 :
    ST2DNT excel function demo 2
    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 :
    immediate-window-vbe
    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.

    module code excel ST2DNT

    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.
    Save addin in XLStart Folder ST2DNT
  8. That’s it. Now use the formula in any workbook.
    ST2DNT final demo

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

One thought on “Excel Function to Convert F91300 SCHSTTIME in JDE to DD/MM/YY HH:MM:SS Format”

  1. Hi Nimish,

    Thanks for posting this article :)

    Is there a way to extract all scheduled jobs with their accurate frequency like yearly/monthly/weekly/daily/day on a particular day/date & time?

    Thanks
    Srikanth G

Leave a Reply

Your email address will not be published. Required fields are marked *