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.

Sum a range of time for billing and analytics when the data contains a date time.

New Mac user here.


I download a high volume of data for billing purposes. A small sample:


I have formatted B2 to include a date format to demonstrate that there is a date hiding in the data.


To calculate the task's billable amount (D) I have found the TIMEVALUE function works:

TIMEVALUE(TIME(HOUR(B2) ,MINUTE(B2),SECOND(B2)))×B3×24


For other reporting purposes I want to create a subtotal and grand total of this time without taking extra steps to convert the data if possible. As I mentioned, there is a high volume of data included in this exercise.


So far, all my attempts to include any duration functions have resulted in errors.


Any recommendations, including on my existing TIMEVALUE approach will be most appreciated. My thanks in advance.

MacBook Pro

Posted on Aug 25, 2022 9:41 AM

Reply
Question marked as Top-ranking reply

Posted on Aug 25, 2022 12:02 PM

HI PW77,


What you are missing is the distinction between "time and date," which in Numbers means 'time of day, and includes the date, of a specific instant in the passage of time, such as 9:41AM on August 25, 2022 (the time in California at which you posted this question), and Duration, which is a quantity, (measured in weeks, days, hours, minutes, seconds, and milliseconds) expressing the difference between the date and time which something started and the date and time that same something ended.


TIMEVALUE is a function that ignores the Date part of a Date and Time value and converts the Time part to a decimal fraction number representing the fraction of the day that has passed between midnight and the value in the Time part of that Date and Time value. Multiplied by 24, the result is a number representing the number of hours and fraction of an hour that has passed in that time, which you've used, with the rates in column C to determine the billing amounts in column D.


Your TIMEVALUE formula could be greatly simplified to: TIMEVALUE(B2)*24


While that works, it doesn't record the data you want to record.


Making 'time of day' stand in for 'duration' will always give you the undesired Date part of a Date and Time value in your entries in column B.


Formatting those cells to contain duration values, or simply entering the values in a format Numbers will reliably recognize as a duration, will make things simpler and less error prone.


Enter the durations in B2-B4 as:

1h 7m 11s

1h 59m 20s

1h 32m 22s


Enter Rates as they are now - numbers formatted as currency.


In D2: DUR2HOURS(B2)*C2


Fill down to other rows containing 'Task' items.



Moving the Subtotal calculation to an added column will simplify the Grand total sum, which will then be the simple total of the column containing the individual task amounts or of the column containing the subtotals.


OR you could leave task amounts and subtotals in the same column, use SUM(D)/2 (in a Footer Row) to total that column, and divide the total by 2 to counteract the fact that the grand total adds each task amount twice—once as a task amount, the second time as part of the subtotal in which it is included.


Regards,

Barry

3 replies
Question marked as Top-ranking reply

Aug 25, 2022 12:02 PM in response to PeaceWarrior77

HI PW77,


What you are missing is the distinction between "time and date," which in Numbers means 'time of day, and includes the date, of a specific instant in the passage of time, such as 9:41AM on August 25, 2022 (the time in California at which you posted this question), and Duration, which is a quantity, (measured in weeks, days, hours, minutes, seconds, and milliseconds) expressing the difference between the date and time which something started and the date and time that same something ended.


TIMEVALUE is a function that ignores the Date part of a Date and Time value and converts the Time part to a decimal fraction number representing the fraction of the day that has passed between midnight and the value in the Time part of that Date and Time value. Multiplied by 24, the result is a number representing the number of hours and fraction of an hour that has passed in that time, which you've used, with the rates in column C to determine the billing amounts in column D.


Your TIMEVALUE formula could be greatly simplified to: TIMEVALUE(B2)*24


While that works, it doesn't record the data you want to record.


Making 'time of day' stand in for 'duration' will always give you the undesired Date part of a Date and Time value in your entries in column B.


Formatting those cells to contain duration values, or simply entering the values in a format Numbers will reliably recognize as a duration, will make things simpler and less error prone.


Enter the durations in B2-B4 as:

1h 7m 11s

1h 59m 20s

1h 32m 22s


Enter Rates as they are now - numbers formatted as currency.


In D2: DUR2HOURS(B2)*C2


Fill down to other rows containing 'Task' items.



Moving the Subtotal calculation to an added column will simplify the Grand total sum, which will then be the simple total of the column containing the individual task amounts or of the column containing the subtotals.


OR you could leave task amounts and subtotals in the same column, use SUM(D)/2 (in a Footer Row) to total that column, and divide the total by 2 to counteract the fact that the grand total adds each task amount twice—once as a task amount, the second time as part of the subtotal in which it is included.


Regards,

Barry

Aug 26, 2022 12:55 PM in response to Barry

Thank you Barry and SG for your responses!


I have solved my issues by combining your TIMEVALUE simplification in a conversion column then reformatted the report to leverage a footer row's capabilities while still producing section subtotals. Good news is that it is working and invoices are going out.


As the old says says, "There is 100 ways to skin a cat!" (Sorry, cats!!) With your input, I stepped back and allowed simplicity to enable an quick and elegant solution that is tolerable with the volume of weekly data that is collected.


My thanks again!!

Sum a range of time for billing and analytics when the data contains a date time.

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