Convert Number to Date & Time

[Former Member]

I need somebody to help me convert a number into date and time. My formula does get the date right but the time is off especially around midnight.

EXAMPLE

Date Created:-  20,131,015.00 (This should be converted to 10/15/13)

Time Created: - 1,949.00 (This should be 12:09:49 AM, but I'm getting 1:09:49 AM)

Time creared:- 449.00 (should be 12:04:49 AM, but I'm getting 4:04:49 AM)

Here is my code

//This function will take a date field AND a time field and give one Date/Time field

//V1 will be the date field, V2 is the time field

Function (numberVar v1, numberVar v2)

//Date Conversion

local stringVar MyDate := CStr(v1);

local stringVar YearVar := MyDate[1 to 2] + MyDate[4 to 5];

local stringVar MonthVar := MyDate[6] + MyDate[8];

local stringVar DayVar := MyDate[9] + MyDate[10];

//Time Conversion

local numbervar MyTimeNum:= Int(v2);

Local stringvar MyTimeStr := cstr(MyTimeNum);

if MyTimeNum<100000 then MyTimeStr := "0"+MyTimeStr;

Replace(MyTimeStr,",","",1);

//fill in array

local stringVar TimeHold := MyTimeStr;

local stringVar Allzeros := "00000000";

Replace(TimeHold,".","",1);

local numberVar z := Length(Timehold);

local stringVar HrsVar := mid(Timehold,1,2);

local stringVar MinVar := Mid(Timehold,z-7+1,1)+mid(Timehold,z-6+1,1);

local stringVar SecVar :=mid(Timehold,z-5+1,2);

DateTime(ToNumber(YearVar), ToNumber(MonthVar), ToNumber(DayVar),

ToNumber(HrsVar), ToNumber(MinVar), ToNumber(SecVar));

[abhilash_kumar]

Hi Phylis,

If you could explain the logic behind

this : Time Created: - 1,949.00 becoming

this: 12:09:49 AM And

this :Time creared:- 449.00

becoming: 12:04:49 AM

maybe we could come up with something.

-Abhilash

[Former Member]

That is how my raw data is. It is stored as a number. So 1,949.00 is supposed to be 12:09:49 AM. I need to convert this to time from number.

Here is my time code but it not working right. Instead of the final result being 12:04:49 AM, I get 1:04:49 AM.

//Time Conversion

local numbervar MyTimeNum:= Int(v2);

Local stringvar MyTimeStr := cstr(MyTimeNum);

if MyTimeNum<100000 then MyTimeStr := "0"+MyTimeStr;

Replace(MyTimeStr,",","",1);

//fill in array

local stringVar TimeHold := MyTimeStr;

local stringVar Allzeros := "00000000";

Replace(TimeHold,".","",1);

local numberVar z := Length(Timehold);

local stringVar HrsVar := mid(Timehold,1,2);

local stringVar MinVar := Mid(Timehold,z-7+1,1)+mid(Timehold,z-6+1,1);

local stringVar SecVar :=mid(Timehold,z-5+1,2);

DateTime(ToNumber(YearVar), ToNumber(MonthVar), ToNumber(DayVar),

ToNumber(HrsVar), ToNumber(MinVar), ToNumber(SecVar));

I hope this helps.

Thanks.

[abhilash_kumar]

What I'm trying to understand is, how does 1949 become 12:09:49 AM?

If you could help with the logic, we could suggest a code that does the conversion.

-Abhilash

[Former Member]

I'm so sorry. I had a typo. 1949 should be 12:19:49. My apologies.

[Former Member]

Were you able to find a solution to my problem?