News and Announcements icon

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... When I Import Time fie...

Finance | Project Operations, Human Resources, ...

Suggested answer

When I Import Time field from Excel to AX Tmp Table Its coming as 0 or 12:00:00 AM

(0) Share

Report

Report

Posted on by Karthickks

100

Hi Team,

When I Import Time from Excel to AX it is coming as 0 or 12:00:00 AM.

info(strFmt("%1", time2str(cells.item(row,7).value().time(), TimeSeparator::Colon, TimeFormat::AMPM)));

In Excel row 7 has Time Format: 5:32:00 AM

--- Thanks In advance.

All responses Img

All responses (2)

Answers Img

Answers (0)

Suggested answer

Sumit Loya 2,230 on at

Like (0)

Report

RE: When I Import Time field from Excel to AX Tmp Table Its coming as 0 or 12:00:00 AM

Hi Karthick,

The code cells.item(row, 7).value().time() may not convert the data as expected if the cell in the excel is not properly formatted. The ideal way would be to do in following way:

COMVariant variant = cells.item(currentRow, 3).value();
switch (variant.variantType())
{
    case COMVariantType::VT_R4 :
        realValue = _variant.float();
        break;

    case COMVariantType::VT_R8 :
        realValue = _variant.double();
        break;

    case COMVariantType::VT_DECIMAL :
        realValue = _variant.decimal();
        break;

    case COMVariantType::VT_BSTR :
        timeHour24 = str2time(_variant.bStr());
        break;

    case COMVariantType::VT_EMPTY:
        break;

    default:
        throw error(strfmt("@SYS26908", _variant.variantType()));
}

timeHour24 = any2int(#secondsPerDay * realValue);
nmaenpaa 101,156 on at

Like (0)

Report

RE: When I Import Time field from Excel to AX Tmp Table Its coming as 0 or 12:00:00 AM

Coming where? In your table, or infolog?

"Time" type in AX is a number that represents seconds from midnight. Your Excel import might not give it to you in this format.

Do you have any other code than the one that you shared?

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2

Martin Dráb 230,214 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans