Glad I found this video. I was looking for a way to enter a start time and a stop time and have Numbers calculate the elapsed time. This video didn't solve that exactly, but the video's explanation of how numbers handles dates and times was key in figuring it out. (FWIW it's simple subtraction, as long as the times entered in the cells being subtracted are formatted correctly.) Thanks!
Thanks for posting this video. It taught me about the DUR2HOURS function, so I could convert a duration to the number of hours, eg 08:30 (8 hours and 3 minutes) to 8.5.
Thanks for the video. Is there a way to add duration to a time without creating a separate column for the duration? Trying to save space in the spreadsheet. :)
@@macmost Thank you. Just tried it. I tried with uneven times, (finish time - start time) = 5h 45m. And an uneven pay rate ($17.67). (I had a check column with the numerical equivalents (such as 5.75 instead of 5h 45m…) off to the side). Worked like a charm.
Thank you for your precious content. I need your help! How do I split data across separate tabs/sheets? Basically I kinda need to create a pivot table my self because I need to elaborate more data than allowed from that. I can't figure it out! Thank you
hello Gary , you have already been a great help 🙏 i have one quistion , for time i type the 00:00 format but is it possible to just type 0000 and that hey automatically make it 00:00 ? thanks
THANK you for this video. It helped a lot. What if I am developing black and white film, and I have to add 20% to my develop time. (9 minutes + 20%). How does this work in Numbers? Could you please help? Thank you.
What sort of calculations? A time isn't something you can multiple or divide (what is half of 2:30 in the afternoon?) but you can certainly add something (specifically a duration) to time. See this very video.
Use the DURATION function. So if B2 is 120 (column B is Miles) and C2 is 120 (column C is mph) then D2 could be DURATION(0,0,B2/C2) where the parameters of DURATION are weeks, days, hours. Then set D2's format to Duration/Custom Units with the 00:00:00 style.
Thank you for your video, I have one question tho... Is it possible to multiple a duration with a currency ? Here is an exemple : 3h30m x 50€/h = 175 €
Numbers doesn't understand the €/h (euros per hour) notation. You also have the base value of a duration being minutes. So here's what you do: Use the duration value as-is. For the other value, use just 50, no units. Then set the formatting of that cell to a custom format with #,### €/h (You add the bit with the #,### from the items below, but type €/h). SO the "Actual" value in the cell is 50, but it displays as 50 €/h. Then you calculate the result with DUR2HOURS(B2)*C2 or whatever the cell references are in place of B2 and C2. This will give you 175 in that cell. You can then set that cell format to currency if you like.
Do you answer questions? My boss wants me to keep track of employee sick days (actually hours) over a rolling 12 month period. Can you show how to do that or show a link to an example of something similar?
@@macmost The tough part is the rolling period. Any 12 month period. So it’s not just Jan to Dec but any 12 consecutive months for a group of 10 people.
@@artcorob One column would contain the date. Another column would be a formula to see if the date was within the last 12 months, so something like IF(YEARFRAC($A1,NOW(),1)≤1,"current","") The YEARFRAC function will give you. value of 1 or less if the date is less than or equal to 1 year before now. Read the function description carefully to fine-tune it. Now you have the word "current" for each row that is within the last year. You can filter on that or just use other functions like SUMIF or COUNTIF to perform calculations...
so im trying to figure out if I have a formula that is counting how many days an item has been listed for since the listing date (I got that down) but I want it to stop counting once I've marked the item as sold?? how would I do this? thank you so much for your videos !!! they are greatly appreciated ❤
Use an IF function. Have a column with the sold date. If the cell in this column is empty, then calculate the current date minus the listing date. If the cell has a date in it, calculate the sold date minus the listing date.
Can you use this for a schedule? For example, let’s say I have 10 tasks with different durations, equaling a total amount of time. Could I make it so that each task has a checkbox that when checked, deducts the amount of time remaining from all the tasks that I have?
Yes. If D2-D11 have the durations of tasks and D12 is a footer row with the total using SUM(D). Then you have E2-E11 as checkboxes. Then E2 can be SUM(D)-SUMIF(E,true,D) which will take the total then subtract the values in D where the cell in E is checked.
Please teach us how to calculate time then values - for example Ending time minus starting time would give us duration and then let’s suppose we were paying by the hour for that duration and want to multiply it by let’s say X dollars per hour? This is really tricky can you show us how to do it?
@@macmost I will watch her tutorial again and will try it… I very much appreciate this… I’m not sure why I couldn’t do it before but I’ll either succeed or I will offer a further questions thank you so much for being so responsive
I don't know about Numbers, but in another app (which I won't mention), we could get a duration on years by dividing by 365.25 (we could carry this out several decimal points). Does this work in numbers?
Do you mean just divide by 365.25? Of course you can divide by anything you want in Numbers. It is just division. But that doesn't really solve the problem unless you are doing astronomical orbital calculations. Usually you want an accurate result that matches the calendar and deals with leap days the right way.
@@macmost To translate a duration in stated days to years, you can divide by the average number of days in a year, 365.25. There are more complicated formulae to calculate the duration out to 4-6 decimal places (and even farther; in a 1990's class, I was given the task to develop a formula to calculate a table of employee seniorities to the minute), but unless you are calculating with start and stop times, where one minute equals 0.000694 days, the error introduced by the use of 365.25 is probably irrelevant. BTW, further research, done since I posted my original comment, shows that this method does indeed work with numbers. I always learn a lot by watching your videos, although I usually have to pause and repeat to follow you. I'm slowing down a bit at 80.
Great video! Do you know if it is possible to select a cell, move the screen to another part of the sheet, change selected cell with arrow keys without the screen moving back to the selected cell?
@@macmost I have one table with long set of data and another summary table that reference to the long data set. I want to check if the references are accurate by scrolling through the long data set while switching cells in the reference table with the arrow keys. Now, every time I switch cell in the reference table, the screen is pulled to the reference table and I need to go all the way down the long data set to find where I want to be.
@@macmost Yes, it’s complicated. Basically, I need to be able to change selected cells outside of the scope of the screen without the screen moving back to the cell.
Hi buddy, love your videos. Thank you. Need help. I have one column in numbers where I simply just want to enter the time by adding 4 digits i.e. 02:45 or 18:37. I would like the semicolon to come in automatically without me having to type on the semicolon. I have tried but failed...can you advice pls?
@@macmost Thanks for responding. I just wanted to quickly enter the time without having to type in the colon. It is ok I can be a little less lazy and make the effort. Thanks anyway.
So you have a text string that includes a decimal value for the time? And you want to have it read like "6:40:31 12/17/2021 GMT +08:00"? You'd need to use a formula to string the number after the decimal, like TEXTBEFORE. Then you'd need to feed that into DATEVALUE for the date and TIMEVALUE for the time and add them together. Then you'd need to add or subtract a DURATION value of 8 hours. Then you'd need to format it as you like, in a custom format, so you can append the "+08:00" as text to it. If you just didn't have the decimal part, then you can skip the formula entirely and just create the custom format.
This is very informative as all your videos however are you on a certain shirt? For instance I was doing that the other day and some lines were adding up and some weren’t I wanted to totals across but yet some lines were given to me somewhere and I’m
2:00 is not a duration value, but a time ("two o'clock"). If you want to have the duration of 2 minutes and 0 seconds, then 2m is the value. If you mean 2 hours, then 2h. So is B2 is 2m and C2 is 3m 15s, then =B2+C2 will give the result 5m 15s.
@@macmost Is it possible to add 2m and 3m15s in one equation? Sometimes, we might not want to have cell B2 and C2 separately to make the sum, but simply do the calculation in one equation.
@@punkysuen What's the "why" behind that? Why add 2m and 3m15s when you can just type 5m 15s? Why are there two separate durations? Maybe I can think of a solution if I had the full picture here.
@@macmost I use Numbers to record running data. Usually, I do one time per day, so I can for example put 3.2km in one cell, and put 15:40 in another cell. The next cell is to calculate the pace. However, in some days I would do two times, and I want to put the total distance and total time for two cells rather than create more sets of cells by the number of training in a day. It is because the whole set of data and analysis relies on 7 days structure, it would be a problem to create extra cells.
@@punkysuen I don't know any way to do that. Because of the unique formatting of durations, in-cell calculations don't work. You'll either need to do the math in your head, or use extra cells. I personally would put each "run" on a separate row. But if you really need to keep the rows as one day per row, then why not have columns like: Run 1 Distance, Run 1 Duration, Run 2 Distance, Run 2 Duration, Run 3 Distance, Run 3 Duration. Then total them up in any formulas you need, or have two additional columns: Run Total Distance, Run Total Duration? You'd get the added benefit of seeing when you ran more than one time per day which could be useful.
How do I calculate the number of days of specific year from a date range. So for example I want to know how many days between 19/02/15 - 14/01/16 but only the days in 2015. Thanks
Hard to suggest something without knowing the situation. Why can't you just calculate the days between Feb 15 2015 and Jan 1 2016? Why even care about the other days if you just want to know from one date until the end of the year?
@@macmost Hi thanks for getting back. I have spreadsheet of Biomass pellet deliveries. It shows the date of the order (DDMMYY), the quantity (tonnes) and Price. Im trying to work out how much it costs per day per year and whether we are using less or more. Some of the periods run across years. For example in 2017 there were 4 purchases. The last was on 22nd November, for 4.57 tonnes, the next was in Feb 2018. Can the system calculate how many days there were in 2017 between both periods and work out the average consumption of pellets (in tonnes) per day? Happy to show you spreadsheet
@@lj8463 Your a create a formula to do it. First, ask yourself how would YOU calculate it. How would you do it on a piece of paper? Work that out, and then implement it in your spreadsheet as a formula. For instance, I would get the start date and if that was less than the first of that year, I would use the first day of the year instead. I would get the end date, and if that was past the end of the year I would use the last day of the year instead. So MIN and MAX functions for the dates. So if the dates were in B2 and C2 then something like this: =MIN(C2,DATE(2017,12,31))−MAX(B2,DATE(2017,1,1))+1 I would add the 1 to make 1 day count (May 15 to May 15 would equal 1 day).
Anyone know how to take the duration between two dates and multiply it by a number and then show the result as a currency? For example: 9 days * $5 per day = $45 Thanks!
Nothing special needed. If B2 and C2 are the dates and D2 is the $/day value, then (C2-B2)*D2 will get you the result since the duration value will be in days.
Select the cell(s), in the Format, Cell sidebar change the duration format to hr+min and then set the Style to 0:00. For 19.75 it gets tricky. You can switch to Number format, but that is in days, not hours.
@@macmost sorry. In a cell say D2 I have a tick box and when I add a comment to that cell and Hover over it it shows a comment and it shows the top right corner of cell a colour yellow in my case
@@macmost thank you. I was using excel but since watching your videos a couple a weeks ago I have moved over to numbers. So thanks for the videos and insights on how to get best out of my Mac.
You can subtract two dates. So if the person's birthday is in C2, then NOW-C2 gets you the number of days since they were born. Then just format the cell for years only. Or use YEARFRAC with the start date as the birthday, the end date as NOW. Then round down if you need just an integer.
great video one of many I have seen from you... I wonder if you could offer me some help with something I did not find you touched on... calculation duration of time but as a number with decimal point... something like time clock... Say someone clocks in at 06:59 and clocks out at say 09:17, how do I extract duration from these two times... something like 2.2h or whatever these to actually arrived at... I played and played with it and can not figure it out... I am semi retired and I drive a school bus... trying to create a table where I can enter my in and out times and want the table to tell me how long my day or week actually was... hope it is possible and I trust you can help :) thank you in advance Gary.
@@macmost Tried using DUR2HOURS... maybe I am not using it right... I have two cells that contain time in and time out... in another cell I am entering a function you suggested (DUR2HOURS) and I use both cells containing data DUR2HOURS(D4,E4) and still get an error... Im sure you know what Im doing wrong :) thanks again in advance...
@@macmost I believe the issue is with the fact that this function (DUR2HOURS) calculates hours based on duration... the data I have are time IN (e.g. 06:30) and Time OUT (e.g. 10:00) which should result in 2.5h... I hate to keep bugging you and I appreciate your fast response... any other suggestions or instructions?
@@derekinaz If time out is E4 and time in is D4 then you would calculate the duration as E4-D4. Do then put DUR2HOURS around that to get 2.5 instead of 2h 30m. So DUR2HOURS(E4-D4).
@@macmost Hello . Thanks for answering. In the menu options under File tin new there is a menu options for spreadsheets. The is one call " My Stocks" . In that sheet, there is a 30-days History table where it shows only the weeks days from Monday to Tuesday. and hides the rows for Saturday and Sundays. I would like to know how they manage to do it. The table is for only 30 days I want to design one for more months.
@@invcark They are doing it there in a way specific to stocks. If you look at the stock price formula you'll see it uses ISERROR to put a "-" when there is no stock price for that day. Then a filter is applied to that table to filter out rows that have a "-" in the price. So it removed weekends, and also stock market holidays. But if you are not tracking stocks, then another solution is needed. That's why I wanted you to use macmost.com/ask and describe exactly what you want to do so I can maybe suggest something that applies to your situation. Please do that as it is not easy to explain things like this in video comments.
@@macmost For example from schedule times and dates I found durations for each day and duration less than 1 hour ı want it to be labeled in an other cell for that specific day (date) “S1” duration more than and equal to 1 hour but less than 2 hours “S2” and so on until 5-10 hours. 00:01-00:59 “S1” 01:00-01:59 “S2” 02:00-02:59 “S3”
@@S3RKCN Just subtract the time from NOW() and you get a duration. Compare that to a duration like one hour: DURATION(0,0,1,0,0,0). So if the time is in B2, then IF(B3−NOW()
@@macmost actually duration does not work like that I have 1-30 days of durations. I want to categorize them like I have described above. Feb 1st duration 1:30 “S2” feb 2nd duration 2:30 ”S3” likewise 29 days and rows …thanx in advance you are great ! 👍🤘
For me you didn’t solve my problem. I buy water at a rate of 1,035 l/min. If I want to know the amount of water between to dates I calculate: (date2-date1)*24*60 => the result is a duration I.s.o. a number. I only could get a number (representing the total amount of water received) by adding +0 at the end of the formula. Que pasa? (sorry I live in Spain)
You are subtracting one date from another, that gives you a duration, yes. Multiplying it by a number keeps it at a duration. Numbers has no idea you are looking at l/min. Actually, multiplying the result gives you days. So Jan 3, 2022 - Jan 1, 2022 = 2 days. Multiply that by 24*60 and you get 2,880 DAYS. You want minutes. If you simply search the functions lists you'll quickly find DUR2MINUTES. So DUR2MINUTES(date2-date1) gives you the number of minutes as a number (not a duration). Use that.
Dilemma: "Time Conversion for Payroll". E.g. I worked 26h 45m x $17; Numbers calculates correctly at $454.75. But PAYROLL apps (of which I've no control) instead calculate this as 26.45 x $17 = $449.65 shorting me $5.10. Because, instead of seeing 45 as 'Minutes', it sees it as 'Decimal Hours' (e.g point 45) meaning they are only calculating 27 of the 45 minutes (*See pdf conversion chart). HOW may I correctly calculate the Decimal Hours so my pay = the time I've actually worked? TY in Advance! hr.wp2.olemiss.edu/wp-content/uploads/sites/93/2017/03/PayrollTimeConversionChart.pdf
@@macmost Lol, understood. I'm asking, is there a formula FUNCTION, to INSERT, like a 'DUR2HOURS', that converts Minutes to Decimals 45m to .75 (26h 45m | 45M = 3/4 hr, 3/4 = .75)? So receivers of my spreadsheets, who CLEARLY don't comprehend the conversion of fractions to decimals, don't have to do it inaccurately themselves, & pay ALL I've earned. (*As a freelancer, you'd be shocked at the number of "accountants" who constantly read 45m as .45 & pay only the 27min.)
@@macmost Mmm no, I don't need to convert to SECONDS. I need a formula that automatically inputs the Decimal Time Conversion. Showing: 26h 45m converted to --> 26.75. Sadly, I can't trust PAYROLL apps (or rather the Accounting 😱people) TO do the CORRECT plain math (or even look it up). Hence, I seek a formula CONVERTING Hours/Minutes to DECIMAL for these people, does this make sense? They see 26h 45m & incorrectly convert that to 26.45. (instead of 26.75) Inputting a function to convert H/M to Decimal would save massive time having (nearly always) to correct/explain fractions of the hour, to decimals & getting paid properly & on time. Tks again.
@@TheJoker-zt4nk Oh, so you want them to input 2.45 when they mean 2m 45s instead of 2 and 45/100 seconds? Wouldn't you be afraid that some people would enter 2.75 because they understand decimals and others 2.45 if they don't? Maybe then the best thing is to just as for a start and end time. Or, ask for minutes and seconds in separate cells? That would be the safe way to go. Otherwise, you could strip out the number and just get the decimal part with MOD(cell reference,1) and then divide that by 0.6, then add back the whole number with FLOOR. So for B2 it would be =MOD(B2,1)/0.6 + FLOOR(B2,1)
Another well thought out and well presented useful video. Thank you
Glad I found this video. I was looking for a way to enter a start time and a stop time and have Numbers calculate the elapsed time. This video didn't solve that exactly, but the video's explanation of how numbers handles dates and times was key in figuring it out. (FWIW it's simple subtraction, as long as the times entered in the cells being subtracted are formatted correctly.) Thanks!
Could really use your help. How are they to be formatted?
I'd love to know how you did it since I can't figure it out myself either!
You are the BEST Gary ! The Host with the Most !
Thank you, Gary! 👏❤️
Thanks for posting this video. It taught me about the DUR2HOURS function, so I could convert a duration to the number of hours, eg 08:30 (8 hours and 3 minutes) to 8.5.
Thank you for your video. I have a question. How can I sum an hours that I make a monthly
Awesome Gary, so clearly explained! Thank you!
Wow. Apple makes Numbers SO easy, compared to Excel. Thanks!
Letting us know why you can’t use “months” (because all months don’t have the same number of days in them) made this whole video worth watching
Thanks for the video. Is there a way to add duration to a time without creating a separate column for the duration? Trying to save space in the spreadsheet. :)
Great tutorial as usual.
What about something like a payroll calculation? [duration worked x pay rate?]
Try it. If one cell contains 5h and another $40 and you multiply them, it gives you $200.
@@macmost Thank you. Just tried it.
I tried with uneven times, (finish time - start time) = 5h 45m. And an uneven pay rate ($17.67). (I had a check column with the numerical equivalents (such as 5.75 instead of 5h 45m…) off to the side).
Worked like a charm.
Thank you for your precious content. I need your help! How do I split data across separate tabs/sheets? Basically I kinda need to create a pivot table my self because I need to elaborate more data than allowed from that. I can't figure it out! Thank you
that's implemented much nicer than in Excel
Excuse me for a question. Does excel trigger your knowledge or is it compatible with it?
hello Gary , you have already been a great help 🙏 i have one quistion , for time i type the 00:00 format but is it possible to just type 0000 and that hey automatically make it 00:00 ? thanks
Not really. It will interpret that as 0, and just a number.
THANK you for this video. It helped a lot. What if I am developing black and white film, and I have to add 20% to my develop time. (9 minutes + 20%). How does this work in Numbers? Could you please help? Thank you.
Hi! is there a way to perform calculations on times? as in excel thank you very much!
What sort of calculations? A time isn't something you can multiple or divide (what is half of 2:30 in the afternoon?) but you can certainly add something (specifically a duration) to time. See this very video.
Can I add a Popup Calendar Date Picker in Numbers? If so, how? I can’t find it anywhere. Thank you.
No. There isn't one.
Gary, is there a way get a cell in a new document created from a template to autofill with the date the document is created? Thanks for your help!
No easy way to do that, no.
Hi , How can you calculate the duration of a trip like 120 mills at 120 M/h = 01:00:00 ?
Use the DURATION function. So if B2 is 120 (column B is Miles) and C2 is 120 (column C is mph) then D2 could be DURATION(0,0,B2/C2) where the parameters of DURATION are weeks, days, hours. Then set D2's format to Duration/Custom Units with the 00:00:00 style.
Thank you for your video, I have one question tho... Is it possible to multiple a duration with a currency ? Here is an exemple : 3h30m x 50€/h = 175 €
I've managed to make it work using time, but I am struggling using duration...
Numbers doesn't understand the €/h (euros per hour) notation. You also have the base value of a duration being minutes. So here's what you do: Use the duration value as-is. For the other value, use just 50, no units. Then set the formatting of that cell to a custom format with #,### €/h (You add the bit with the #,### from the items below, but type €/h). SO the "Actual" value in the cell is 50, but it displays as 50 €/h. Then you calculate the result with DUR2HOURS(B2)*C2 or whatever the cell references are in place of B2 and C2. This will give you 175 in that cell. You can then set that cell format to currency if you like.
Thanks bunches
Do you answer questions? My boss wants me to keep track of employee sick days (actually hours) over a rolling 12 month period. Can you show how to do that or show a link to an example of something similar?
Keep track of how? I mean you can just record them in a table, one sick day per row.
@@macmost The tough part is the rolling period. Any 12 month period. So it’s not just Jan to Dec but any 12 consecutive months for a group of 10 people.
@@artcorob One column would contain the date. Another column would be a formula to see if the date was within the last 12 months, so something like IF(YEARFRAC($A1,NOW(),1)≤1,"current","") The YEARFRAC function will give you. value of 1 or less if the date is less than or equal to 1 year before now. Read the function description carefully to fine-tune it. Now you have the word "current" for each row that is within the last year. You can filter on that or just use other functions like SUMIF or COUNTIF to perform calculations...
@@macmost Thanks - I'll give it a try.
so im trying to figure out if I have a formula that is counting how many days an item has been listed for since the listing date (I got that down) but I want it to stop counting once I've marked the item as sold?? how would I do this? thank you so much for your videos !!! they are greatly appreciated ❤
Use an IF function. Have a column with the sold date. If the cell in this column is empty, then calculate the current date minus the listing date. If the cell has a date in it, calculate the sold date minus the listing date.
@@macmost awesome thank you so so much 🙏☺️
Can you use this for a schedule? For example, let’s say I have 10 tasks with different durations, equaling a total amount of time. Could I make it so that each task has a checkbox that when checked, deducts the amount of time remaining from all the tasks that I have?
Yes. If D2-D11 have the durations of tasks and D12 is a footer row with the total using SUM(D). Then you have E2-E11 as checkboxes. Then E2 can be SUM(D)-SUMIF(E,true,D) which will take the total then subtract the values in D where the cell in E is checked.
@@macmost thank you so much!
Please teach us how to calculate time then values - for example Ending time minus starting time would give us duration and then let’s suppose we were paying by the hour for that duration and want to multiply it by let’s say X dollars per hour? This is really tricky can you show us how to do it?
Do it just like you said. Subtract times to get the duration, multiple by a dollar amount to get dollars.
@@macmost I will watch her tutorial again and will try it… I very much appreciate this… I’m not sure why I couldn’t do it before but I’ll either succeed or I will offer a further questions thank you so much for being so responsive
I don't know about Numbers, but in another app (which I won't mention), we could get a duration on years by dividing by 365.25 (we could carry this out several decimal points). Does this work in numbers?
Do you mean just divide by 365.25? Of course you can divide by anything you want in Numbers. It is just division. But that doesn't really solve the problem unless you are doing astronomical orbital calculations. Usually you want an accurate result that matches the calendar and deals with leap days the right way.
@@macmost To translate a duration in stated days to years, you can divide by the average number of days in a year, 365.25. There are more complicated formulae to calculate the duration out to 4-6 decimal places (and even farther; in a 1990's class, I was given the task to develop a formula to calculate a table of employee seniorities to the minute), but unless you are calculating with start and stop times, where one minute equals 0.000694 days, the error introduced by the use of 365.25 is probably irrelevant. BTW, further research, done since I posted my original comment, shows that this method does indeed work with numbers. I always learn a lot by watching your videos, although I usually have to pause and repeat to follow you. I'm slowing down a bit at 80.
Great video! Do you know if it is possible to select a cell, move the screen to another part of the sheet, change selected cell with arrow keys without the screen moving back to the selected cell?
So move the selection "blind?" No, there's no way to do that. What's the situation?
@@macmost I have one table with long set of data and another summary table that reference to the long data set. I want to check if the references are accurate by scrolling through the long data set while switching cells in the reference table with the arrow keys. Now, every time I switch cell in the reference table, the screen is pulled to the reference table and I need to go all the way down the long data set to find where I want to be.
@@JacobDaniel. Hard to visualize, but maybe only use the pointer when doing it, not the keyboard?
@@macmost Yes, it’s complicated. Basically, I need to be able to change selected cells outside of the scope of the screen without the screen moving back to the cell.
@@JacobDaniel. If by change you mean manually type into the cell, then no, you won't be able to type into the cell without it being visible.
How can I get the time difference from dates to hours by decimal?
Get the difference as a duration by subtracting. Then surround that in DUR2HOURS()
Hi buddy, love your videos. Thank you. Need help. I have one column in numbers where I simply just want to enter the time by adding 4 digits i.e. 02:45 or 18:37. I would like the semicolon to come in automatically without me having to type on the semicolon. I have tried but failed...can you advice pls?
I can't think of a way to do that, at least not easily. Why don't you want to type the colon? Also, are these times or durations?
@@macmost Thanks for responding. I just wanted to quickly enter the time without having to type in the colon. It is ok I can be a little less lazy and make the effort. Thanks anyway.
Need help here. How to convert 2021-12-17 06:40:31.983 in UTC to GMT+8 time format? Thanks in advance.
So you have a text string that includes a decimal value for the time? And you want to have it read like "6:40:31 12/17/2021 GMT +08:00"?
You'd need to use a formula to string the number after the decimal, like TEXTBEFORE. Then you'd need to feed that into DATEVALUE for the date and TIMEVALUE for the time and add them together. Then you'd need to add or subtract a DURATION value of 8 hours. Then you'd need to format it as you like, in a custom format, so you can append the "+08:00" as text to it.
If you just didn't have the decimal part, then you can skip the formula entirely and just create the custom format.
This is very informative as all your videos however are you on a certain shirt? For instance I was doing that the other day and some lines were adding up and some weren’t I wanted to totals across but yet some lines were given to me somewhere and I’m
Sorry, I don't understand your question.
@@macmost I think she means "using a special sheet" instead of "beeing on a certain shirt" 🙂
How can I add two durations in a function? For example, I want to calculate 2:00 + 3:15, but the colon : cannot be directly type in a function.
2:00 is not a duration value, but a time ("two o'clock"). If you want to have the duration of 2 minutes and 0 seconds, then 2m is the value. If you mean 2 hours, then 2h. So is B2 is 2m and C2 is 3m 15s, then =B2+C2 will give the result 5m 15s.
@@macmost Is it possible to add 2m and 3m15s in one equation? Sometimes, we might not want to have cell B2 and C2 separately to make the sum, but simply do the calculation in one equation.
@@punkysuen What's the "why" behind that? Why add 2m and 3m15s when you can just type 5m 15s? Why are there two separate durations? Maybe I can think of a solution if I had the full picture here.
@@macmost I use Numbers to record running data. Usually, I do one time per day, so I can for example put 3.2km in one cell, and put 15:40 in another cell. The next cell is to calculate the pace.
However, in some days I would do two times, and I want to put the total distance and total time for two cells rather than create more sets of cells by the number of training in a day. It is because the whole set of data and analysis relies on 7 days structure, it would be a problem to create extra cells.
@@punkysuen I don't know any way to do that. Because of the unique formatting of durations, in-cell calculations don't work. You'll either need to do the math in your head, or use extra cells. I personally would put each "run" on a separate row. But if you really need to keep the rows as one day per row, then why not have columns like: Run 1 Distance, Run 1 Duration, Run 2 Distance, Run 2 Duration, Run 3 Distance, Run 3 Duration. Then total them up in any formulas you need, or have two additional columns: Run Total Distance, Run Total Duration? You'd get the added benefit of seeing when you ran more than one time per day which could be useful.
How do I calculate the number of days of specific year from a date range. So for example I want to know how many days between 19/02/15 - 14/01/16 but only the days in 2015. Thanks
Hard to suggest something without knowing the situation. Why can't you just calculate the days between Feb 15 2015 and Jan 1 2016? Why even care about the other days if you just want to know from one date until the end of the year?
@@macmost Hi thanks for getting back. I have spreadsheet of Biomass pellet deliveries. It shows the date of the order (DDMMYY), the quantity (tonnes) and Price. Im trying to work out how much it costs per day per year and whether we are using less or more. Some of the periods run across years. For example in 2017 there were 4 purchases. The last was on 22nd November, for 4.57 tonnes, the next was in Feb 2018. Can the system calculate how many days there were in 2017 between both periods and work out the average consumption of pellets (in tonnes) per day? Happy to show you spreadsheet
@@lj8463 Your a create a formula to do it. First, ask yourself how would YOU calculate it. How would you do it on a piece of paper? Work that out, and then implement it in your spreadsheet as a formula. For instance, I would get the start date and if that was less than the first of that year, I would use the first day of the year instead. I would get the end date, and if that was past the end of the year I would use the last day of the year instead. So MIN and MAX functions for the dates. So if the dates were in B2 and C2 then something like this:
=MIN(C2,DATE(2017,12,31))−MAX(B2,DATE(2017,1,1))+1
I would add the 1 to make 1 day count (May 15 to May 15 would equal 1 day).
@@macmost Many thanks. Im using Mac. I have dates in cells so can the dates be cells so I can copy the formula down the page?
@@macmost It gives me 365 days for a date range of 22/11/17 (A14) and 06/02/18 (A15). Im doing something wrong clearly!
why is by numbers not allowed to block cells? just the hole table?
I multiply duration 570m x 1.250 and comes out to 712m 30s instead of 712.50?
How to convert the minute duration into number?
Put the function DUR2MINUTES around it. So =DUR2MINUTES(A1*B1)
@@macmost problem solved. You’re a genius 🎉👍🏼🙂
Anyone know how to take the duration between two dates and multiply it by a number and then show the result as a currency?
For example: 9 days * $5 per day = $45
Thanks!
Nothing special needed. If B2 and C2 are the dates and D2 is the $/day value, then (C2-B2)*D2 will get you the result since the duration value will be in days.
How would you display a duration like 19:45 as 19.75?
Select the cell(s), in the Format, Cell sidebar change the duration format to hr+min and then set the Style to 0:00. For 19.75 it gets tricky. You can switch to Number format, but that is in days, not hours.
Random question. Can you change the comment colour in the top of a box as shows a yellow tiny triangle at the minute needs to be darker to see
Can you explain what you mean by a "comment?" That could apply to a lot of things and I'm not sure which from your description.
@@macmost sorry. In a cell say D2 I have a tick box and when I add a comment to that cell and Hover over it it shows a comment and it shows the top right corner of cell a colour yellow in my case
@@davidevans4052 Go to View, Comments, Author Color.
@@macmost thank you. I was using excel but since watching your videos a couple a weeks ago I have moved over to numbers. So thanks for the videos and insights on how to get best out of my Mac.
How would you convert time difference to minutes"?
Don't convert. Just get the duration as normal. Then change the Cell Format to only show minutes.
Falto un ejemplo para calcular EDAD. Como se expresan años ?? Numbers parece que no tiene esa opcion ...
You can subtract two dates. So if the person's birthday is in C2, then NOW-C2 gets you the number of days since they were born. Then just format the cell for years only. Or use YEARFRAC with the start date as the birthday, the end date as NOW. Then round down if you need just an integer.
@@macmost Muchisimas gracias bro!
great video one of many I have seen from you... I wonder if you could offer me some help with something I did not find you touched on... calculation duration of time but as a number with decimal point... something like time clock... Say someone clocks in at 06:59 and clocks out at say 09:17, how do I extract duration from these two times... something like 2.2h or whatever these to actually arrived at... I played and played with it and can not figure it out... I am semi retired and I drive a school bus... trying to create a table where I can enter my in and out times and want the table to tell me how long my day or week actually was... hope it is possible and I trust you can help :) thank you in advance Gary.
Use the DUR2HOURS function.
@@macmost thank you will give it a try... appreciate the response
@@macmost Tried using DUR2HOURS... maybe I am not using it right... I have two cells that contain time in and time out... in another cell I am entering a function you suggested (DUR2HOURS) and I use both cells containing data DUR2HOURS(D4,E4) and still get an error... Im sure you know what Im doing wrong :) thanks again in advance...
@@macmost I believe the issue is with the fact that this function (DUR2HOURS) calculates hours based on duration... the data I have are time IN (e.g. 06:30) and Time OUT (e.g. 10:00) which should result in 2.5h... I hate to keep bugging you and I appreciate your fast response... any other suggestions or instructions?
@@derekinaz If time out is E4 and time in is D4 then you would calculate the duration as E4-D4. Do then put DUR2HOURS around that to get 2.5 instead of 2h 30m. So DUR2HOURS(E4-D4).
How can I design a table with only working days from Monday to Friday and skip Saturday and Sunday.
I'll need more details to help you there. Use macmost.com/ask and describe exactly what you want to do.
@@macmost Hello . Thanks for answering. In the menu options under File tin new there is a menu options for spreadsheets. The is one call " My Stocks" . In that sheet, there is a 30-days History table where it shows only the weeks days from Monday to Tuesday. and hides the rows for Saturday and Sundays. I would like to know how they manage to do it. The table is for only 30 days I want to design one for more months.
@@invcark They are doing it there in a way specific to stocks. If you look at the stock price formula you'll see it uses ISERROR to put a "-" when there is no stock price for that day. Then a filter is applied to that table to filter out rows that have a "-" in the price. So it removed weekends, and also stock market holidays. But if you are not tracking stocks, then another solution is needed. That's why I wanted you to use macmost.com/ask and describe exactly what you want to do so I can maybe suggest something that applies to your situation. Please do that as it is not easy to explain things like this in video comments.
@@macmost could you tell me how did they placed the filter. I can’t seem to see it.
@@macmost Thanks !! you are a Genius!! I just checked . I have been trying to figure this out for almost 3 years!!
how should I use “if” for durations
Depends what you are doing. Give me your example.
@@macmost For example from schedule times and dates I found durations for each day and duration less than 1 hour ı want it to be labeled in an other cell for that specific day (date) “S1” duration more than and equal to 1 hour but less than 2 hours “S2” and so on until 5-10 hours. 00:01-00:59 “S1” 01:00-01:59 “S2” 02:00-02:59 “S3”
@@S3RKCN Just subtract the time from NOW() and you get a duration. Compare that to a duration like one hour: DURATION(0,0,1,0,0,0). So if the time is in B2, then IF(B3−NOW()
@@macmost actually duration does not work like that I have 1-30 days of durations. I want to categorize them like I have described above. Feb 1st duration 1:30 “S2” feb 2nd duration 2:30 ”S3” likewise 29 days and rows …thanx in advance you are great ! 👍🤘
For me you didn’t solve my problem.
I buy water at a rate of 1,035 l/min.
If I want to know the amount of water between to dates I calculate: (date2-date1)*24*60 => the result is a duration I.s.o. a number.
I only could get a number (representing the total amount of water received) by adding +0 at the end of the formula. Que pasa? (sorry I live in Spain)
You are subtracting one date from another, that gives you a duration, yes. Multiplying it by a number keeps it at a duration. Numbers has no idea you are looking at l/min. Actually, multiplying the result gives you days. So Jan 3, 2022 - Jan 1, 2022 = 2 days. Multiply that by 24*60 and you get 2,880 DAYS. You want minutes. If you simply search the functions lists you'll quickly find DUR2MINUTES. So DUR2MINUTES(date2-date1) gives you the number of minutes as a number (not a duration). Use that.
@@macmost Thank you! I feel stupide that i didn’t see it myself. I enjoy your videos very much.
I’m trying at add hours and minutes but my sums always add up to into days how do only use Hrs and Minutes in the sum 😂help I’m dying over here
See the part about formats, starting at around 8:02. You just need to define in the format that you want only hours and minutes, not days.
My Audio died 2 minutes in. Strange.
Dilemma: "Time Conversion for Payroll". E.g. I worked 26h 45m x $17; Numbers calculates correctly at $454.75. But PAYROLL apps (of which I've no control) instead calculate this as 26.45 x $17 = $449.65 shorting me $5.10. Because, instead of seeing 45 as 'Minutes', it sees it as 'Decimal Hours' (e.g point 45) meaning they are only calculating 27 of the 45 minutes (*See pdf conversion chart). HOW may I correctly calculate the Decimal Hours so my pay = the time I've actually worked? TY in Advance! hr.wp2.olemiss.edu/wp-content/uploads/sites/93/2017/03/PayrollTimeConversionChart.pdf
Just use plain math. There are 60 seconds in a minute. So divide the number of seconds by 60. So 45/60 = 0.75. 45 seconds is 0.75 minutes.
@@macmost Lol, understood. I'm asking, is there a formula FUNCTION, to INSERT, like a 'DUR2HOURS', that converts Minutes to Decimals 45m to .75 (26h 45m | 45M = 3/4 hr, 3/4 = .75)? So receivers of my spreadsheets, who CLEARLY don't comprehend the conversion of fractions to decimals, don't have to do it inaccurately themselves, & pay ALL I've earned. (*As a freelancer, you'd be shocked at the number of "accountants" who constantly read 45m as .45 & pay only the 27min.)
@@TheJoker-zt4nk Yes, exactly. Use DUR2SECONDS to convert it to seconds and then divide that by 60 to give 45.75 from 42m 45s.
@@macmost Mmm no, I don't need to convert to SECONDS. I need a formula that automatically inputs the Decimal Time Conversion. Showing: 26h 45m converted to --> 26.75. Sadly, I can't trust PAYROLL apps (or rather the Accounting 😱people) TO do the CORRECT plain math (or even look it up). Hence, I seek a formula CONVERTING Hours/Minutes to DECIMAL for these people, does this make sense? They see 26h 45m & incorrectly convert that to 26.45. (instead of 26.75) Inputting a function to convert H/M to Decimal would save massive time having (nearly always) to correct/explain fractions of the hour, to decimals & getting paid properly & on time. Tks again.
@@TheJoker-zt4nk Oh, so you want them to input 2.45 when they mean 2m 45s instead of 2 and 45/100 seconds? Wouldn't you be afraid that some people would enter 2.75 because they understand decimals and others 2.45 if they don't? Maybe then the best thing is to just as for a start and end time. Or, ask for minutes and seconds in separate cells? That would be the safe way to go. Otherwise, you could strip out the number and just get the decimal part with MOD(cell reference,1) and then divide that by 0.6, then add back the whole number with FLOOR. So for B2 it would be =MOD(B2,1)/0.6 + FLOOR(B2,1)