Converting the HHHH:mm:ss values in an excel file to a date field
I have a unique problem with converting the HHHH:mm:ss to integer values in datameer. I did an import job in datameer from an excel file that has the raw data for total hours in a day/week/month for Calls Answered in a call center. The values for a particular month, when i bring it into datameer as a DATE data type, from the excel file looks like this
Excel Hours for a Month
|
5077:35:47 Datameer July 29, 1900 01:35:47 PM |
I tried to strip the date timestamp by converting it into int values for hours, minutes and seconds respectively, using format date.
int(formatdate(#ImportJob_Test!HandleTime;"mm"))*60
It worked for minutes and seconds. However, for hours i am running into an issue. It is calculating the hours based on the time stamp for a given 24 hours a day value. So if the time stamp states 01:35:47 pm, it is giving me hours between the ranges of 12-24. How do i tackle this issue?
-
Hi Syed,
In order to help you with your requirement, I would like to understand this use case better.
Could you please clarify on below points. Let me know if I missed something.
- You have the raw data (input) that indicates the total time a call center spent on the line during a certain period Eg: a day/ a week/ a month.
- This Data is aggregated into values like `HHHH:MM:ss` eg: 5077:35:47, that means the call center spent 5077 hr, 35 min and 47 sec on the line. Could you please provide a few sample data here from Excel for the reference?
- This data was ingested into Datameer from Excel using an import job on the DATE data type.
If my assumption is correct, the next question I would like to ask is, what you need to do with this data in Datameer? Please clarify for what you need to use it and how to transform it.
Regards,
Sabeel
Please sign in to leave a comment.
Comments
1 comment