Hey guys:
I hope someone can help me. I'm frustrated trying to get this formula to work. Any help is greatly appreciated.
I have a travel report that calculates our per diem.
It's based on partial days.
Days are divided into four parts, 12:00 a.m. - 6:00 a.m., 6:00 to 12:00 p.m., 12:00 p.m. - 6:00 p.m., and 6:00 p.m. - 12:00 a.m.
I need to calculate the number of days between two dates, plus the quarter days. Say I leave on 6:00 p.m. on April 13th and return at April 15th at 5:00 p.m. then I get a quarter day on the 13th (.25) (6:00-12:00) and then a whole day on the 14th (1.00 days) and three quarters on 15th (.75.) So I would be gone 2.00 days. We would take that number and multiply times the per diem to come up with how much the employee is owed.
Dates and times can be in separate fields.
I hope I'm explaining this ok. Any ideas?
Thanks for looking at this.
Blake Harrell
I have a problem with my time sheet not giving me the proper value after midnight.. here is the code im using... =TEXT((E4-B4)+(I4-F4),"h:mm")
I'm using Excel 2003 on WinXP
I have some data that I pasted into excel from a website. Everything is coming in formatted as general. One of the columns is a time and is formatted as HH:MM(A/P), so 08:58P or 12:06A, for example. I want to use an if statement on this data, but I think Excel needs to recognize it as a time first. If I try to reformat the time column to a time format like "13:00" or "13:30:55", nothing happens.
Using some Left, Right, Concatenate statements, I could get the data to look like 8:58 PM or 12:06 AM, which I then copy paste as values into a new column. I try to format this column as "13:00" (Or any other format for that matter) and nothing happens. But THEN if I double click in the cell to edit it, then push enter without changing anything, excel recognizes it as a time and changes it to whatever format I want...what gives?
I could write a macro to go in and enter each cell(the F2 key), then exit it, to get Excel to recognize it as a time, but that seems clunky....
Thanks
I am trying to create an issues log that includes the aging of issues that are in "open" or "in progress" status for the following periods of time:
0-30 days
31-60 days
61-90 days
91-120 days
over 120 days
The aging should be from the date the issue was opened to the current date (including weekends). Once the aging is calculated, i want to be able to count the number of issues that fall within each range.
I'm trying to figure out the best way to track days off that employees earn throughout the year. The way it works is every year starting fresh for each 60 consecutive work days an employee is given a "Lieu Day" which is basically a bonus day off. Starting Jan. 1 if someone doesn't miss a day of work all year they would accumulate 4 Lieu Days. If someone has an unscheduled day off work, then the count starts over on their first day back to work. This happens each time an employee has an unscheduled day off. If an employee has work 80 consecutive days they would have earned 1 Lieu Day. If they request a day off and want to use their Lieu Day, I would like to be able to track this as well. For example, an employee hasn't had an unschedule day off all year and has worked 200 consecutive work days, but has used a Lieu Day; ideally the spreadsheet would show me that they currently have 2 Lieu Days available and are 40 days away from earning their next Lieu Day.
Hopefully this isn't too confusing. Any help would be great.
Thx
To : Mr Excel
Hi Mr.Excel i want to ask you a question, i have an assignment that required me to block my excel's cell. Specifically speaking, we can still edit the cell for two days and it will lock itself automatically after two days. Can you tell me how to do it?
thanks a lot ^^
Hi,
I have a timesheet that I enter time in on a daily basis. The time sheet calculates my time for the day. The problem I am having is that when I enter time such as 12:00, the worksheet automatically selects Am. If I enter 12:00 AM and I enter another time such as 9:00, it will make that time 9:00 AM. I want to know how to keep the time following in the correct pattern, ie if i enter 12:00 PM , then enter 1:00 in the next cell it will make the time 1:00 PM not 1:00 AM. Any help is appreciated.
Thank You,
Cranetech
I am having trouble trying to get a range of time such as an employee schedule to break down into single units. For example I want this employee's schedule to break down into trackable labor hours.
Monday
IN
OUT
HOURS
Marorie
8:00 AM
6:00 PM
10.00
I have another sheet n my workbook that tracks labor hours over a day and I am trying to get it to populate based upon the schedule tool I use.
Name
5:00am
6:00am
7:00am
8:00am
9:00am
10:00am
11:00am
12:00pm
1:00pm
2:00pm
3:00pm
4:00pm
5:00pm
6:00pm
7:00pm
8:00pm
9:00pm
10:00pm
Marjorie
0.5
1
1
1
1
0.5
1
1
1
8
So basically I want my schedule worksheet to populate my labor hour sheet if Marjie is scheduled from 8:00 am to 6:00pm then a value of 1 populates under each hour she is scheduled.
Any ideas for an easy way to work this?? I have tried numerous IF functions and just cant get it to work.
Guys, help a blonde please :o).
Cell A1 - 01/07/07
Cell B1 - 3 PM
Cell C1 - 01/08/07
Cell D1 - 10 AM
I need a time difference (in minutes) between these 2 days (c+d) - (a+b) that subtratcs 990 minutes of overnight (talking 7.5 working day from 9 am to 5.30 pm).
How whould I do it keeping the data the way it's entered?
Thanks!
I just need to figure out how to figure out time in hours and minutes for when the person's time goes past midnight and into the next day. How can I set up a formula for showing me how long the person worked in hours and minutes.
