Unanswered

Get Hours from hh:mm:ss format from data fetching from CSV file

(0) Share

Report

Posted on by AkshayManke

Hi, I am fetching data from a CSV file and pasting into a SharePoint list column. I want to convert the format for example if the value in CSV file is 05:30:00 then it should be added in the column as 5.30, If it is 2:00:00 then 2 should be added. How can i achieve this please?

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

nichtaurelius 50 on at

Like (2)

Report

Hi AkshayManke

For your case you can use the following expression:

concat(int(split("YOUR TIME", ':')[0]), '.', split("YOUR TIME", ':')[1])

What it does it uses the split funtcion with the ":" as the seperator so we can get the hours and minutes from the string. Then we put a concat and get the value formated as a string back.
Make sure to put in your time in "YOUR TIME" and then you should be good to go 🙂

I hope that i could help with your issue

Greetings,
Aurelius 🙂

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (1)

Report

@nichtaurelius I just tested your answer and it worked. Good Job 👍

@AkshayManke I wanted to ask what kind of column are you putting this into in SharePoint? Is it a type single line text or is it a number type?

If it is a number type, I'm curious if you want 05:30:00 to be 5.3 or should it be 5.5 since 30 would be half of an hour? I don't know much about your data and everything, but just something I thought about.

Was this reply helpful? Yes No
AkshayManke 2 on at

Like (0)

Report

Hi @nichtaurelius & @wskinnermctc,

Thanks a lot, to both. I added the below steps as per your suggested expressions and got the expected results.

@wskinnermctc, Comments below on your queries please.

I wanted to ask what kind of column are you putting this into in SharePoint? Is it a type of single line text or is it a number type?
Yes, I am using a SharePoint list, and the column type is single line of text.

If it is a number type, I'm curious if you want 05:30:00 to be 5.3 or should it be 5.5 since 30 would be half of an hour? I don't know much about your data and everything, but just something I thought about.
Fow now I want, if the value in CSV file is 05:30:00 then I should get 5.30. Which we will interpret five hours and thirty minutes. However, your point is valid and if you have any workaround to divide the minutes and show the show the 05:30:00 to 5.5 hrs. then I would be glad to know how to get those results.

Many Thanks to both once again.!!

Was this reply helpful? Yes No