+ Reply to Thread
Results 1 to 12 of 12

Subtracting minutes from a fixed time in a cell

  1. #1
    Registered User
    Join Date
    02-23-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Post Subtracting minutes from a fixed time in a cell

    So, here's the problem.

    I'll need to create a spdreasheet that must be able to do the following.
    Insert the task that was done, the date, and how much time was spent to do that task.
    The customer have 4 hours monthly that we give them, so we will do tasks for the customer based in this 4 hours. We go there, do the task, like 30, 25 minutes, and will insert on the spreadsheet, the spreadsheet must be able to when I insert this 20 minutes withdraw this minutes from the 4 hours. So far looks kinda simple and can be solved, BUT, the problem is that is monthly, so next month he will have 4 hours again, not cumulative. Next moth he/she will have just 4 hours, and we don't bill them for those hours.

    Problem 2, in the same spreadsheet we must calculate this, when the 4 hours finish, we will calculate a 15-minute increment. Example:
    4 hours have finished. Now we will do a 12 minutes task for the customer, the formula should count this: verify how many minutes and if is it smaller then 15 will consider as 15 and multiples per 0.75
    If we do a task for them, and spend 16 minutes, the formula should calculate as 30 minutes and multiple that for the correct price, multiple for 0.75.

    We give new customer a plus of 4 hours during the first month, so the first month the customer have 8 hours free.

    I have attached the spreadsheet, so you can check what I've done so far.

    Any small help will be good.
    Attached Files Attached Files
    Last edited by zekkk; 02-24-2011 at 11:57 AM. Reason: It was needed to bring back the thread due to one change on the spdreasheet.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Subtracting minutes from a fixed time in a cell

    What is "hours included left" (Col E)?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-23-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Subtracting minutes from a fixed time in a cell

    If is a new customers, he/she will have 4 included hours. We just give this 4 hours during the first month, the spreadsheet I attached is of a customer that is not new, so he just has 4 hours and 0 included left.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Subtracting minutes from a fixed time in a cell

    Okay, here's what I did
    In Col D (D2) =IF(ROW()=2,4/24,IF(MONTH(B2)>MONTH(B1),4/24,""))
    This will give another 4 if entering a new month.

    In Col F(F2) =IF(ISNUMBER(D2),CEILING(D2+E2-C2,15/1440),CEILING(MAX(0,F1+E2-C2),15/1440))
    I allow you to enter values into column E at any time (possible credits to customer)
    If value falls below 0, it will show 0 here.

    In Col G (G2) =IF(ROW()=2,0,IF(F2=0,IF(F1=0, CEILING(C2,15/1440),CEILING(ABS(F1+E2-C2),15/1440)),0))
    CEILING rounds up to next multiple of 15 minutes. Does this work for you?
    Attached Files Attached Files
    Last edited by ChemistB; 02-24-2011 at 11:29 AM.

  5. #5
    Registered User
    Join Date
    02-23-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Subtracting minutes from a fixed time in a cell

    Man, Excel cells are on your blood :D, there's just one thing.

    Multiply the extra hours, on the ROW G per our billing time. We bill 0.75 per minute.
    As we bill customers per month, we will generate an invoice for each extra task done beyond the 4 hours. It's almost perfect, just need something like this.
    Example:

    Configuring SMTP Service | 2/23/2011 | 34 |

    I'd like that collum H calculate how much will be for the customer. It should multiple 0.75 per minute or 45 per hour.
    Thank you very much for the help

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Subtracting minutes from a fixed time in a cell

    First format column H to Currency, then in H2

    =IF(MONTH(B3)>MONTH(B2),G2*24*45,0)

    It gives a total for that month at the end of each month. Does that work for you?

  7. #7
    Registered User
    Join Date
    02-23-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Subtracting minutes from a fixed time in a cell

    Thanks man that solved my problem.

    Really appreciate your help :D

  8. #8
    Registered User
    Join Date
    02-23-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Subtracting minutes from a fixed time in a cell

    Sorry man to bother you again, but after showing to my boss he said to change just one thing.
    Hours left should also have 15 minutes increment. I was looking up what you did and trying to develop my own formula, but I failed
    Column H should calculate the billing times, doesn't matter if the month has end or not. I just noticed that.

    See attached, when turns the month, everything brake.
    Attached Files Attached Files
    Last edited by zekkk; 02-23-2011 at 05:35 PM. Reason: Found that column H doesn't calculate billing hours if the month has not end.

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Subtracting minutes from a fixed time in a cell

    Do you want column H to calculate a value for each row or just the last row (even if not the end of month).
    For example if row 7 is an extra hour and 8 is an extra hour and 9 is an extra hour, do you want $45 in cells H7, H8 and H9 or do you want $135 in H9 with H7 and H8 empty OR do you want $45 in H7, $90 in H8 and $135 in H9?

  10. #10
    Registered User
    Join Date
    02-23-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Subtracting minutes from a fixed time in a cell

    I want $45 in cells H7, H8 and H9, each row calculated independent. But the problem here is that when turns the month, in Column D 4 hours are added for each sub-sequent cell. Last thing Hours Left should have 15-minutes increment.
    This thing has gave me headache :S

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Subtracting minutes from a fixed time in a cell

    Okay, I modified the attachment and equations in Post #4 above. Is that what you are looking for? I use CEILING which always rounds up (i.e. 16 minutes goes to 30, not to 15). If you want rounding, replace CEILING with MROUND.

  12. #12
    Registered User
    Join Date
    02-23-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Subtracting minutes from a fixed time in a cell

    Yeah, that really solved my problem. Thanks Excel's God.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1