Trying to show Days and Hours in a cell

Welcome to the forum.

It would help if you attached a sample Excel workbook with a few examples of your data and showing clearly what you want to achieve - follow the instructions in the yellow banner at the top of the screen for how to attach a workbook (don't try to use the paperclip icon, as it doesn't work on this forum).

Hope this helps.

Pete

I have added a sample worksheet with my formulas. I know E4 has a different formula, I was trying to see if that would work.

First of all, you are not using a formula to change the display - you are using a Custom Format.

Secondly, all your totals seem to be correct - ignore what it says in the formula bar.

So, what exactly is the problem?

Pete

Sorry Pete, like I said I am still very new, I guess I mixed up format with formula, honest mistake. But regardless are you able to see why the format is not working in some cells but is working in others?
For example, cell E3 I am trying to get it to show 2d 9h but when I type 02:09 for the dd hh format, it is coming up as 0d 2h

That is because if you enter 02:09 into a cell then Excel concludes that you have entered a time of 2 hours and 9 minutes, and because of your formatting this then shows as zero days and 2 hours. You need to appreciate that formatting does not change the actual value of a number - instead it only affects how that number is displayed.

It would also help you if you realise that the way Excel treats dates and times is to consider dates as the number of elapsed days since some reference date (actually 1st January 1900) and are thus whole numbers, and times are fractions of a 24-hour day, so that 12:00 is stored internally by Excel as 0.5, whereas 6:00am is represented by 0.25 and 6:00pm (or 18:00) is stored as 0.75. Also, Excel is programmed to recognise certain characters during input and tries to make sense of what has been entered. So, in USA the three parts of a date are usually separated by the symbol / and are considered to be in month-date-year order, so if you type in the string of characters 12/31/21 then Excel recognises that as a date representing 31st December 2021, as any year part less than 30 is taken to be in the current century. However, if you were to enter 13/31/21 then Excel would return this as a text value, as it would not be able to make sense of there being 13 months. Other special characters include the colon : which is used to separate times into hours-minutes-seconds (in that order), and Excel will make allowance for more than 24 hours or more than 60 minutes or seconds, rounding up to the next higher unit as necessary. So, if you enter 25:15 into a cell which is formatted to show days and hours, then Excel will assume that you have entered 25 hours and 15 minutes and zero seconds and show it as 1d 1h.

In conclusion, if you wanted to enter a value of 2 days and 9 hours into a cell, you can do it as:

1/2/1900 9:0:0

to avoid any ambiguity. With the formatting that you have applied, this would then show as 2d 9h.

Slightly less typing would be to enter it as 57:0, and this would also show as 2d 9h (i.e. 57 hours and zero minutes). In the formula bar it would show as 1/2/1900 9:0:0, although you didn't enter that.

Hope that begins to make it a bit clearer (though dates and times can be very confusing).

Hope this helps.

Pete