Home

Removing Days from dd:hh:mm:ss Fields

gaz1858
New Contributor

A report provided by one of our suppliers gives us times in dd:hh:mm:ss format. When I try to add these together, Excel will only do it if i remove the dd part and convert the format to hh:mm:ss. 

Is there an easy way of removing the dd part across multiple cells? Up until now I have been manually going in and deleting it which isn't manageable.

I have attempted just formatting the field/s in question but nothing happens.

Thanks in advance

6 Replies
Can you give an example of your data?

If you'd like to show only time part, it's enough to remove dd: part in format.

If you'd like to sum all times ignoring the date part you may use the formula like

=SUMPRODUCT(A1:A200-INT(A1:A200))

applying [hh]:mm:ss format to the result

 

Capture.PNG

 

Here is the data as it is delivered to me. The only way I can get the times to add up is to manually go in and remove the initial 00: '

 

Even using the other persons suggestion below did not work until i removed these zeros

Thank you Sergei,

 

I have given this a try however it only worked when I removed the initial 00: manually again.

 

I have attached a pic of the data as it is delivered to me on a seperate response to see if that helps with any solutions

It looks like you have texts in form of time. When you may use

image.png

 

Yes it seems that way. Thanks Sergei

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies