You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Why are cells that I want to remain formatted as 'durations' automatically converting to 'numbers'?

First, I appreciate your time.


Second, the issue and question:


I am creating a running total for time entrances based on specific jobs. In 2 different cells, on the same row, I am entering my time (in 0:00 format) when I start and finish a specific job. In another cell, the duration for that "job" is calculated, on that same row. At the bottom of these "duration" calculations for the specific jobs, are the SUMS of said "durations" giving me a total amount of hours worked during the week. There is an issue with the "SUM action". Here is a picture to help visualize.



My problem is: The durations are converting over to numbers automatically and disrupting the ability to sum (green box) when I do not have times entered into the "work hours" (start and finish) categories. I would much rather the SUM not factor in the cells without entries and continue to do it's work with the cells that do have entries giving me a total number of hours worked whether I use all of the rows or not.


How do I accomplish this?

MacBook Pro (2020 and later)

Posted on May 16, 2021 6:42 AM

Reply
Question marked as Top-ranking reply

Posted on May 16, 2021 7:17 AM

I believe it is the empty rows, the ones that have a zero in that column, that are your problem. Blank cells are usually either ignored by math formulas or treated as numeric zeros. In a row with no start and stop times, the result of your stop time - start time will be 0 - 0 = 0 (numeric value) regardless of how you format the cell that has the formula. Your choices are to make a formula that defaults to a 0 duration if you have not entered the times or one that forces the answer to be a duration.


This one will not do the calculation until you have entered the stop time

=IF(D<>"",D−B,DURATION(0))


This one will force it to be a duration.

=DURATION(0)+(D−B)


The advantage to the first one is there won't be an error triangle after entering the start time and before entering the stop time.


If you do not want to see 0:00 in the empty rows you can do

=IF(D≠"",D−B,"")

SUM will sum up the column, ignoring the text in these cells. "" is text, it is a null string. The formula won't work if the any other formula requires that cell to be a duration (unless you do the same kind of thing in that formula using an IF statement).

Similar questions

2 replies
Question marked as Top-ranking reply

May 16, 2021 7:17 AM in response to SamAdam21

I believe it is the empty rows, the ones that have a zero in that column, that are your problem. Blank cells are usually either ignored by math formulas or treated as numeric zeros. In a row with no start and stop times, the result of your stop time - start time will be 0 - 0 = 0 (numeric value) regardless of how you format the cell that has the formula. Your choices are to make a formula that defaults to a 0 duration if you have not entered the times or one that forces the answer to be a duration.


This one will not do the calculation until you have entered the stop time

=IF(D<>"",D−B,DURATION(0))


This one will force it to be a duration.

=DURATION(0)+(D−B)


The advantage to the first one is there won't be an error triangle after entering the start time and before entering the stop time.


If you do not want to see 0:00 in the empty rows you can do

=IF(D≠"",D−B,"")

SUM will sum up the column, ignoring the text in these cells. "" is text, it is a null string. The formula won't work if the any other formula requires that cell to be a duration (unless you do the same kind of thing in that formula using an IF statement).

Why are cells that I want to remain formatted as 'durations' automatically converting to 'numbers'?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.