Hi everyone hope it's an excellent day.
I did find something similar to what I was looking for on the site but don't quite understand what they were doing.
https://www.mrexcel.com/forum/excel...t.html?highlight=splitting+cell+based+content
This is my problem. I get a usage report from a vendor and in column C is the time a user spent logged on. The problem is sometimes
Column D has 1 hr 12 min 23 sec and sometimes it has 14 minutes and sometimes it's just 50 sec. The times vary. When the file is downloaded as a SCV the format of the column is general. I have tried converting text to columns but that sometimes puts hrs min and sec in the same column so if I tried to add the time up for each department it would make 1 hr 1 min 1 sec look like 3 hours. Here is how it looks
A B C D
User Name User Full Name Usage Dept
JoeScnmoe@123.com Joe Schmoe 1hr 15 min 32 sec ACCT
JillSchmoe@123.com Jill Schmoe 38 min FIN
BillieBobb@123.com Billie Bob 55 sec ACCT
If I convert text to columns I would have 94 hrs 15 min 32 sec So far I have tried a combination of text to columns before and after using find and replace with replacing the hr min sec with : I've got roughly 600 users in this system and I have to calculate each departments usage from a variety of internal and external users. Also formatting seems to be an issue. If I format the cells with hh:mm:ss it won't show anything larger than 24 hours correctly and if I format with d:hh:mm:ss it would show 50 hours as 2 days but will not show the rest of the time correctly. I think if the cells could be split correctly the rest of the issues would fall into place. Any help would be greatly appreciated.
Thanks.
I did find something similar to what I was looking for on the site but don't quite understand what they were doing.
https://www.mrexcel.com/forum/excel...t.html?highlight=splitting+cell+based+content
This is my problem. I get a usage report from a vendor and in column C is the time a user spent logged on. The problem is sometimes
Column D has 1 hr 12 min 23 sec and sometimes it has 14 minutes and sometimes it's just 50 sec. The times vary. When the file is downloaded as a SCV the format of the column is general. I have tried converting text to columns but that sometimes puts hrs min and sec in the same column so if I tried to add the time up for each department it would make 1 hr 1 min 1 sec look like 3 hours. Here is how it looks
A B C D
User Name User Full Name Usage Dept
JoeScnmoe@123.com Joe Schmoe 1hr 15 min 32 sec ACCT
JillSchmoe@123.com Jill Schmoe 38 min FIN
BillieBobb@123.com Billie Bob 55 sec ACCT
If I convert text to columns I would have 94 hrs 15 min 32 sec So far I have tried a combination of text to columns before and after using find and replace with replacing the hr min sec with : I've got roughly 600 users in this system and I have to calculate each departments usage from a variety of internal and external users. Also formatting seems to be an issue. If I format the cells with hh:mm:ss it won't show anything larger than 24 hours correctly and if I format with d:hh:mm:ss it would show 50 hours as 2 days but will not show the rest of the time correctly. I think if the cells could be split correctly the rest of the issues would fall into place. Any help would be greatly appreciated.
Thanks.
