You are here:

Excel/Working on christmas day


Hi. I have a issue I cant get around. I will try to describe it.  if I am set up to work christmas day, but not working I will get payed for that time. But if I work that day I will get regular pay for time I work from 5 am to 1:15 pm (after that it is 100% overtime pay) plus the time I was set up to work. I am trying to get F to get the same value as in this eks..

  A          B        C        D          E          F
Date        Plan in plan out  clocked in clocked out  Regular pay
1) 24.12.2012   16:00     23:15     12:00     18:00        6,50
2) 24.12.2012   09:00     15:00     08:00     15:00        7,00
3) 24.12.2012   13:00     23:15     07:00     12:00       15,25
4) 24.12.2012   13:00     23:15     13:00     17:00        6,50
5) 24.12.2012   16:00     23:15          7,25
6) 24.12.2012   09:00     15:00     11:00     14:00        5,25

Line 1 rule: regular pay for worked hours from 12 to 13:15 plus planed from 18:00 to 23:15 = 1,25 + 5,25 = 6,5

Line 2 rule: regular pay for worked hours from 08 to 13:15 = 5,25 plus planed from 13:15 to 15:00 = 1,75 (tot 7)

line 3 rule: regular pay for worked hours from 07 to 12 = 5 + plan from 13 to 23:15 = 10,25 tot 15,25

Line 4 rule: regular pay for worked hours from 13 - 13:15 = 0,25 + plan from 17 to 23:15 = 6,25 total 6,5

line 5 rule: plan from 16 to 23:15 = 7,25 (not worked that day, so only pay for plan hours)

Line 6 rule: regular pay for worked hours from 11 to 13:15 plus plan form 09 - 11 and 14 to 15 (2,25 + 2 + 1 = 5,25)

I hope you understand. I have tryed to solved this many time, but I am now stuck.

I hope you can help me

Best regards

I can certainly try- you said Christmas day, but you've typed 24th December - which confuses a little, but I'm also failing to follow the rules - on rule 1 work was from 12 until 6pm, but you've calculated pay from 12 to 1:15pm and then the non worked hours - which I understand, but did you not need to reflect the overtime?  I'm confused about the definition of the Rules - I think if I can get it clear it will become simpler to follow - what is throwing me especially is the difference between rule one and rule 2 as in the one you have ignored the hours worked between 13:15 and the planned out time, in the other you have included them.  This is causing me problems working out the logical flow of the problem, and without that I cannot arrive at a formula that will logically work.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 All rights reserved.