Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for comparing a date to today's date
I am in need of a formula to compare today's date to a previous date and
perform a calculation based on the number of days in between. Oct 1 equipment ran 12 of 16 hours Oct 2 equipment ran 16 of 16 hours Oct 10 Equipment ran 10 of 16 hours I need percentage of today's uptime with equipment assuming every day has 16 available hours. Oct 1 - Oct 10 has 160 available hours for (12+16+...10)/160= 93.7% uptime. I want this to be dynamic so that at any given date I can look at real time percentage. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for comparing a date to today's date
Hi,
If column A:A contains dates and column B:B contains uptime then =AVERAGE(B:B)/16 should give you the uptime percentage, if you format the cell as %. It will be dynamic, as it will change as more values are entered in B:B. This will give you the total uptime since you started monitoring, so naturally with time it should converge to your overall average. Write back if you want something more specific, e.g. the average of the last X days. HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for comparing a date to today's date
I am not familiar with the A:A, B:B notation. Is this an array form? Can I
look into a date in mid cycle to get the cummulative total to date? "vezerid" wrote: Hi, If column A:A contains dates and column B:B contains uptime then =AVERAGE(B:B)/16 should give you the uptime percentage, if you format the cell as %. It will be dynamic, as it will change as more values are entered in B:B. This will give you the total uptime since you started monitoring, so naturally with time it should converge to your overall average. Write back if you want something more specific, e.g. the average of the last X days. HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for comparing a date to today's date
Hi
If you put the start date required in C1 and the end date in D1, then enter in E1 =SUMPRODUCT((A1:A1000=C1)*(A1:A1000<=D1)*(B1:B100 0))/((D1-C1+1)*16) Format E1 as Percentage -- Regards Roger Govier " ft.com wrote in message ... I am not familiar with the A:A, B:B notation. Is this an array form? Can I look into a date in mid cycle to get the cummulative total to date? "vezerid" wrote: Hi, If column A:A contains dates and column B:B contains uptime then =AVERAGE(B:B)/16 should give you the uptime percentage, if you format the cell as %. It will be dynamic, as it will change as more values are entered in B:B. This will give you the total uptime since you started monitoring, so naturally with time it should converge to your overall average. Write back if you want something more specific, e.g. the average of the last X days. HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for comparing a date to today's date
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formulas | Excel Discussion (Misc queries) | |||
insert date | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
todays date formula | Excel Worksheet Functions |