You are here:

Excel/Help in formulating the difference between day/evening/weekend



I work at a home healthcare company which we pay some of our employees depending on when they worked. I am currently using Excel 2007.  We have a rate for normal hours worked during the week (8:00AM-4:59PM M-F), we have a rate for evening hours (5:00PM-7:59AM weekdays only) and we have a rate for weekend hours worked Friday 5:00PM-Monday 7:59AM).  I don't need to figure anything out as far as a rate paid goes I just need a formula that will know if it's a "normal rate" to put the time worked in a specific column, if they worked an evening to put the hours worked in a specific column or if it's a weekend to put it into a specific column.  Individuals do not get both evening and weekend pay (weekends supersede evenings).  So if they're working the weekend and working after 5pm it's still just considered weekend hours.  

I think maybe if there was a way to do a formula that knows if it's a weekday and not an evening to put the rate type as weekday, if it's an evening rate to put it as evening.  If it's a weekend (no matter what the time) it's just a weekend rate.  If I'm not mistaken I believe you can only do 1 true and 1 false for an "IF" formula… I need a formula that can say If it's a weekday and it's after 4:59PM then "Evening", If it's a weekday and between hours of 8-4:59 then "Weekday", and If it's a "Weekend" (no matter the time) then it's a "Weekend"  Does that make sense?  

I can send you my excel sheet to give you a better picture of what I want to do.  I appreciate the help as I've been working on this for sometime and can't seem to figure a proper solution.  Thanks Tom!



of course the cell being examined must contain a date time combination or separte cells with date and time.  You can then use the weekday function in conjunction with the time to make your determination.  It is more complex if you have start and stop times and need to break the time between day and evening of day and weekend.  

yes, send the file

Tom Ogilvy

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


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.