+ Reply to Thread
Results 1 to 5 of 5

Holiday Calculations

  1. 10-28-2018, 08:42 AM #1
    Stephen74
    Stephen74 is offline
    Registered User
    Join Date
    04-13-2017
    Location
    Birmingham
    MS-Off Ver
    2016
    Posts
    31

    Holiday Calculations

    For some reason this didn't post the other day, oh well. Try, try, try again....

    I'm looking to add holiday entitlement to my wages spreadsheet.

    This is for casual staff who work irregular hours. In the UK they earn 7.242 minutes of holiday time for every hour worked, or, 12.07% of what ever time they have worked. So the calculation seems straight forward, hours x 7.242 / 60. (the actual formula suggest is 12.07/100 x hours but excel seems to hate that)

    has anyone else got any suggestions on how to calculate earned holiday entitlement?
    Is there a formula to use proper time calculations to work this out or do i need to convert the time to numbers? (same for hourly pay, can this be done through the time format or do I have to convert time to numbers? ie 3 hours 45 minutes needs to be converted to 3.75 to work out their pay)
    Reply With QuoteRegister To Reply
  2. 10-28-2018, 08:44 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,015

    Re: Holiday Calculations

    In what format is their total weekly time currently shown? Exactly?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-28-2018, 08:47 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,015

    Re: Holiday Calculations

    This works, doesn't it?

    Excel 2016 (Windows) 32 bit
    D
    E
    14
    		 Custom
    15
    		 format
    16
    		 [hh]:mm:ss =D17*12.07%
    17
    58:30:00
    07:03:39
    Sheet: Sheet1
    Reply With QuoteRegister To Reply
  4. 10-28-2018, 09:49 AM #4
    Stephen74
    Stephen74 is offline
    Registered User
    Join Date
    04-13-2017
    Location
    Birmingham
    MS-Off Ver
    2016
    Posts
    31

    Re: Holiday Calculations

    Well would you look at that, people lied to me, they said excel doesn't do % and everything has to be X/100, so i simply never tried %.

    That works perfectly ALiGW, thank you very much.

    ...Spoke to soon perhaps. The calculation works fine, now I have to convert that into wages.

    What I would like the spread sheet to do is work out:

    1. How much holiday they have earnt per month
    2. Add that to any holiday they earn't from the previous month
    3. Subtract any holiday they have taken that month.
    4. Work how much they should be paid for the holiday they have taken.
    5. Show how much holiday is remaining.

    I cant work out pay from time at the moment, as mentioned above it seems I have to convert it to a number first. The rest I can do.

    Issue with time to wages
    7h 45 mins x £10 = £3.23

    Convert to a number, 7.75 and it works out right at £77.50
    Last edited by Stephen74; 10-28-2018 at 09:58 AM.
    Reply With QuoteRegister To Reply
  5. 10-28-2018, 09:51 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,015

    Re: Holiday Calculations

    LOL!!! You live and learn.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Working hour calculations between two dates excluding sunday, holiday and lunch break
    By kush141087 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-02-2017, 03:53 PM
  2. Holiday accrual formula when the holiday year doesn't start in Jan
    By tharding in forum Excel General
    Replies: 5
    Last Post: 02-14-2017, 04:27 PM
  3. working on a holiday planner based on various holiday anniversary dates
    By marktc19 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2017, 09:26 AM
  4. Adding Bank Holiday pay into calculations HELP!
    By Learning ExL in forum Excel General
    Replies: 8
    Last Post: 04-27-2015, 02:47 PM
  5. fixed holiday, floating holiday and night differential hours
    By gerard_gonzales33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-05-2014, 02:33 AM
  6. [SOLVED] IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates
    By jowarks in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-05-2013, 05:42 AM
  7. Holiday accrual calculations
    By philweb in forum Excel General
    Replies: 6
    Last Post: 12-13-2007, 04:01 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1