Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculated value displayed in increments
I'm calculating a value, calculated daily, based on the number of days in two
weeks. How can I get the value to be displayed only in incremements based on two weeks, instead of showing the daily calculations? ie- vacation is accrued as 5.77 hours every two weeks. The formula is (todays date - beginingof the year)/14 * 5.77 but how do I get the value displayed to only rise in increments of 5.77? 2nd question: =if(todayholiday, 7.5,0) the workbook adds 7.5 hrs of "holiday" time to the record if today is past the day of the holiday. If it is before the holiday, the value is 0. How do I get it to add the 7.5 if the date falls between two dates, and otherwise display 0? ie- christmas 2005 is allowed to be used as a 2006 holiday day until 1/31/06. How do I get it to add 7.5 if it is between 12/25/05 and 1/31/06, but display 0 outside of those options? =if(12/25/05<today<1/31/06,7.5,0) is not working for me. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculated value displayed in increments
For the first part, you need to calculate the number of two-week periods that
have passed: =int((today()-date(2005,12,31))/14) (you may need to adjust your 'start date' slightly). Multiply that by the accrual rate of 5.77. Second part requires the AND function to combine two logical tests: =if(and(today()=date(2005,12,25),today()<=date(20 06,1,31)),7.5,0) HTH. --Bruce "golden322" wrote: I'm calculating a value, calculated daily, based on the number of days in two weeks. How can I get the value to be displayed only in incremements based on two weeks, instead of showing the daily calculations? ie- vacation is accrued as 5.77 hours every two weeks. The formula is (todays date - beginingof the year)/14 * 5.77 but how do I get the value displayed to only rise in increments of 5.77? 2nd question: =if(todayholiday, 7.5,0) the workbook adds 7.5 hrs of "holiday" time to the record if today is past the day of the holiday. If it is before the holiday, the value is 0. How do I get it to add the 7.5 if the date falls between two dates, and otherwise display 0? ie- christmas 2005 is allowed to be used as a 2006 holiday day until 1/31/06. How do I get it to add 7.5 if it is between 12/25/05 and 1/31/06, but display 0 outside of those options? =if(12/25/05<today<1/31/06,7.5,0) is not working for me. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use displayed value, not stored value? | Excel Worksheet Functions | |||
Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions | |||
how to create a calculated field from another calculated field? | Excel Discussion (Misc queries) | |||
PivotTable Calculated Item error | Excel Discussion (Misc queries) | |||
How can a calculated value be displayed wiyhin a text string | Excel Discussion (Misc queries) |