Excel/Formula Help

Advertisement


Question
QUESTION: I have a Table spreadsheet:
Header; A: Date, B:Unit, C:Room, D: Employee Name, E: Agency, F: Start Time, G:End Time,H: Hours, I:Break, J:Total hours, K:Total Mins.

I already have some simple formula
H: =IF(G272<F272,G272+1,G272)-F272
K: =J272 *60*24

Format Cells:
Locale: English (US)
F & G: Time, 1:30 PM
H & J: Time, 13:30
K: General

We do business with 4 Agencies; MSN, MS, SOS, FH  all except MSN charge $17.00/hr.

MSN between  the hours of  7am 7 pm  charge $15.00/hr.
and between 7pm 7am charge $ 17.00/hr.  

and in column E: Agency I input MSN, MS, FH, and SOS.

Time I use is in 15 minutes increments, so I only input 7:00 pm, 7:15 pm, 7:30 pm, or 7:45 pm...  

I am trying to find out the amount we should be invoiced for each employee for each day.

Well I hope to hear from you soon.

ANSWER: Hello Peggy,

I have one question - When you are entering the start and end times in the columns F & G, do you enter only the time or do you enter the entire date, month, year, hours and minutes.
I'm assuming if you have this data dumped form some sort of time keeping software, you might be able to get the start and end times along with date. Start and end times with date will make it easier to manage the number of hours. (I know there's a date column but it might differ in case of night shifts or if someone works over a 2 day period)

Also, would you be comfortable with using Macros.

Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Hello Gulshan,

Thank you for your quick response.  No I only enter the times and the times are in 15 minutes increments.  Time I use is in 15 minutes increments, so I only input 7:00 PM, 7:15 PM, 7:30 PM, or 7:45 PM...  

so for start time i will enter 7:00 PM and end time 7:00 am.

I only enter the date once.  If you like I can e-mail you a sample spreadsheet.

There is no software we use, just my spread sheet.  They are not allow to work more that 16 hours.

so for msn if they clock in between 7a -7p the bill us $15.00/hr but if they clock in between 7p-7a then they bill us 17.00/hr.

All others bill us 17.00/hour no matter what time.  I am not familiar with macros.

Thank you for your time

Answer
Hi Peggy,

I've been working on this one for a couple of days now and I have a few problems with the way your data is currently handled:
1. Start time and end time do not have a date. This is ok if you were doing a straight calculation, but the moment you use MSN style billing, it becomes an issue. Eg. start time could be before 07:00, between 07:00 & 19:00 or after 19:00 on either the current day or on the previous day, similarly end time could also be before 07:00, between 07:00 & 19:00 or after 19:00 on either the current day or on the previous day. This gives potentially 36 different combinations which need to be handled ((3*2)*(3*2)). Excel can handle only upto 7 if conditions looped within a single formula.

2. While calculating the MSN charges you will need to track the break time details as well, whether the break was during the day time or during the night time or the break could be from 07:30 to 08:30.

You might need to make changes to the file format like - include date into the start and end times, split the number of hours into day and night sessions of 7-7, track the break timings (or create an assumption that breaks are always during day / night) etc.

If you could define the entire logic in a fool proof manner, I might be able to help you out.

Gulshan.
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.