Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to count weeks from date "X" then calculate
I want to create a field(s) in Excel 2003 that will allow me
to enter a hard (non-changing date) and have it calculate how many weeks have eclipsed since that time and then multiply to 40 hrs per week to give a total of work hours that have passed since the date The Date desired to use is July 1st, 2005 and counting..... TIA, _Bigred |
#2
|
|||
|
|||
Hi!
Try this: A1 = 7/1/2005 =(TODAY()-A1)/7*40 Biff "_Bigred" wrote in message ... I want to create a field(s) in Excel 2003 that will allow me to enter a hard (non-changing date) and have it calculate how many weeks have eclipsed since that time and then multiply to 40 hrs per week to give a total of work hours that have passed since the date The Date desired to use is July 1st, 2005 and counting..... TIA, _Bigred |
#3
|
|||
|
|||
On Fri, 07 Oct 2005 01:06:27 GMT, "_Bigred" wrote:
I want to create a field(s) in Excel 2003 that will allow me to enter a hard (non-changing date) and have it calculate how many weeks have eclipsed since that time and then multiply to 40 hrs per week to give a total of work hours that have passed since the date The Date desired to use is July 1st, 2005 and counting..... TIA, _Bigred With your date in A1, something like =(TODAY()-A1)/7*40 Or if you want to count only workdays at 8 hrs/day, you could use the NETWORKDAYS function. =NETWORKDAYS(A1, TODAY(), holidays) * 8 See HELP for this function. It requires installation of the Analysis Tool Pak and HELP will tell you how to do that. --ron |
#4
|
|||
|
|||
With July 1, 2005 in A1,
Try this in a cell formatted to General or Number: =DATEDIF(A1,TODAY(),"d")/7*40 The base formula returns days, so dividing by 7 will yield full and partial weeks. If you want to only work with full weeks, adjust it to this: =INT(DATEDIF(A1,TODAY(),"d")/7)*40 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "_Bigred" wrote in message ... I want to create a field(s) in Excel 2003 that will allow me to enter a hard (non-changing date) and have it calculate how many weeks have eclipsed since that time and then multiply to 40 hrs per week to give a total of work hours that have passed since the date The Date desired to use is July 1st, 2005 and counting..... TIA, _Bigred |
#5
|
|||
|
|||
Doun't you have any state holdays there at all ?
P.e. I myself have additionally to think about 4 pre-holidays - there are 4 state holidays, for which when preceeding day is workday, it is 5 hours long instead of 8 hours. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "_Bigred" wrote in message ... I want to create a field(s) in Excel 2003 that will allow me to enter a hard (non-changing date) and have it calculate how many weeks have eclipsed since that time and then multiply to 40 hrs per week to give a total of work hours that have passed since the date The Date desired to use is July 1st, 2005 and counting..... TIA, _Bigred |
#6
|
|||
|
|||
how to count weeks from date "X" then calculate
I used the DATEDIF formula below and it works fine. Is there a way I can
limit it's return. Example: It will only calculate the number of hours until a certain date (i.e from 7/1/05 thru 6/29/06) ?? TIA, _Bigred "RagDyer" wrote in message ... With July 1, 2005 in A1, Try this in a cell formatted to General or Number: =DATEDIF(A1,TODAY(),"d")/7*40 The base formula returns days, so dividing by 7 will yield full and partial weeks. If you want to only work with full weeks, adjust it to this: =INT(DATEDIF(A1,TODAY(),"d")/7)*40 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "_Bigred" wrote in message ... I want to create a field(s) in Excel 2003 that will allow me to enter a hard (non-changing date) and have it calculate how many weeks have eclipsed since that time and then multiply to 40 hrs per week to give a total of work hours that have passed since the date The Date desired to use is July 1st, 2005 and counting..... TIA, _Bigred |
#7
|
|||
|
|||
how to count weeks from date "X" then calculate
Hi
=DATEDIF(A1,B1,"d")/7*40 (with end date in B1). Or =DATEDIF(A1,DATE(2006,6,29),"d")/7*40 (with fixed end date) Arvi Laanemets "_Bigred" wrote in message ... I used the DATEDIF formula below and it works fine. Is there a way I can limit it's return. Example: It will only calculate the number of hours until a certain date (i.e from 7/1/05 thru 6/29/06) ?? TIA, _Bigred "RagDyer" wrote in message ... With July 1, 2005 in A1, Try this in a cell formatted to General or Number: =DATEDIF(A1,TODAY(),"d")/7*40 The base formula returns days, so dividing by 7 will yield full and partial weeks. If you want to only work with full weeks, adjust it to this: =INT(DATEDIF(A1,TODAY(),"d")/7)*40 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "_Bigred" wrote in message ... I want to create a field(s) in Excel 2003 that will allow me to enter a hard (non-changing date) and have it calculate how many weeks have eclipsed since that time and then multiply to 40 hrs per week to give a total of work hours that have passed since the date The Date desired to use is July 1st, 2005 and counting..... TIA, _Bigred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Calculate weeks cover | Excel Worksheet Functions | |||
calculate anniversary of date after specified date | Excel Worksheet Functions | |||
Count the occurances of a month in a range of date fields | Excel Worksheet Functions |