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.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I’m trying to make a weekly schedule that tallies up everyone’s weekly hours, however I’m having an issue where the formula sometimes returns a negative number as the cells assigned date is wrong. I’m trying to keep B3:O11 formatted as a Pop-Up Menu however then I don’t think it allows me to change the assigned date… Any tips anyone?
iPad Pro, iPadOS 17
Hi motojoe,
Format the Start times and end times as Text. Then Numbers won't automatically insert a date, but will still treat them as times. The Duration calculation will work.
Regards,
Ian.
Hi motojoe,
Format the Start times and end times as Text. Then Numbers won't automatically insert a date, but will still treat them as times. The Duration calculation will work.
Regards,
Ian.
Format as Text, type in the times, convert to Pop-Up Menus. The Pop-Ups will have Times without Dates.
Regards,
Ian.
For this you need the TIMEVALUE() function.
This takes a Date/Time cell and converts it to a value representing the time (expressed in 24 hour increments), so 12PM would be 0.5, 9 AM would be 0.375, etc.
Once you have these values, you can subtract one from the other to find the time difference. Multiplying this by 24 gets you the number of hours.
For example, if your formula is:
=(TIMEVALUE(G4)−TIMEVALUE(F4))×24
you'll get the time difference (in hours).
Note that because you're stripping the date, this ONLY works for same-day values - if someone is working a graveyard shift, say 10PM to 6 AM, this would return a negative number.
if that's a problem you can add an additional IF() function to check for negative values, and adjust accordingly:
> IF((TIMEVALUE(G4)−TIMEVALUE(F4)) <0,24+(TIMEVALUE(G4)−TIMEVALUE(F4))×24,(TIMEVALUE(G4)−TIMEVALUE(F4))×24)
If the time differences ever span more than 24 hours (what kind of boss are you! :-D) you can use DATEDIF() to calculate the number of days difference, and add that into the mix, but it doesn't sound like that's needed here.
I would but I’m trying to keep them formatted as “Pop-Up Menu” for ease of making the schedule. I was hoping for a way other than simply changing the format.
Ahhhhhhhh, thank you!
Is there a way to calculate the duration of a cell array without including the date?
