Turning Dates Into Weeks? |
Turning Dates Into Weeks? - Excel |
|
I need to turn that date into the week that it falls into within a particular quarter of a year. For example:
1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).
And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).
Can Excel do this? Convert a date to the week it falls within?
Similar Topics
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
example;
8:00 to 8:07 = 8.0 hrs
8:08 to 8:23 = 8.25 hrs
8:24 to 8:38 = 8.5 hrs
8:39 to 8:53 = 8.75 hrs
8:53 to 8:59 = 9.0 hrs
I am having trouble writing an equation that would sum the clock-in and clock-out times (2 per day) and display the total time in these quarter hours and adding an additional hour if it is >= to *:53. Any help would be great.
I'm really hoping someone can help me with this...
I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?
for example: if an employee works 10 hours a day we would not count towards overtime until the employee completes a 40 hour work week.
Any suggestions would be greatly appreciated!!!!!
Thanks,
YV
I'm still new here but after I searched the forum I found great work done
for the needy like me.
So I'm so hopeful
I'm concerned with making a timetable for my school which we used to do it manually
I need a code to distribute each teacher timetable with the following basis:
the number of classes_ the teacher is assinged to work with_multiplied by the number of periods ( which is changeable ).
e.g ; Teacher 1 has 2 classes (1-1 &1-2). Each class should be visited 9 timea a week.
So Teacher 1 should work 18 periods a week
the distribution I need 'd look like the example in Sheet("timetable")
Please don't let me down
any help 'd be greately appreciated
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKED-OR- choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
Is there a way to have the Min and Max values adjusted dynamically for the Scale of the values being displayed ??
I know that I can use named ranges to display various sections of data -- month by month, or quarter by quarter, for example.
But when the value of these ranges vary greatly from section to section I end of having to manually go and adjust the Min and Max values of the chart scale.
For example, if I was looking at a graph of the S&P 500's prices last November, a Min and Max range of 750 to 1200 would be fine. But if I had a dynamic range established and scrolled over to view the S&P 500's prices for this past February, I would need a Min and Max range of around 650 to 850.
Is there a way to have these Min & Max values adjust automatically depending on the values being displayed ??
Thanks
StanSz
Here is the example.
Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- Product XXXXX Actual/Forecast : 174 1674 574 574 524 524 524 524 524 728 728 728 New Purchases : 0 0 0 0 1002 0 0 0 0 0 0 0 Goods In Transit: 0 1506 0 0 0 0 0 0 0 0 0 0 Other Movements : 0 0 0 0 0 0 0 0 0 0 0 0 Closing Stock : 1270 1102 528 -46 432 -92 -616 -1140 -1664 -2392 -3120 -3848 Week Stock in Hand ? ? ? ? ? ? ? ? ? ? ? ?
So, in Month of Jan2011 the Closing stock is 1270, need a formula to calculate how many weeks will this stock cover look at future forecast.
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.
Thanks!
charitydc
All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards...
And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end.
This will be a completely separate formula on the sheet....
Any help?
current date when opened, using today(). Users then Save As to have an
archive copy of their spreadsheet. However, when they go back to access
their saved sheet, the date changes from the created date to the current
date. How do I stop this in Excel?
I know that Word lets you do this by changing the code from {DATE} to
{CREATEDATE}, but I can't find a similar setting in Excel.
Please help and thank you!
But I do not want to insert any sheet for different dates & also the format will not be copied & paste in the same sheet. Just at the place of Date the date will be changed & the datas will be entered manually for each day.
In future, when I shall put the date, the datas will show that particular date's datas only.
Can anyone help me ?
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
inbetween the two dates. Does anyone know how to then convert the number of
days into months?
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
Any help would be very much appreciated
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4
1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power