Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have posted a previous post on how do i calculate the days in YTD and im still stuck but also now i need to know the minutes in YTD in order to calculate the percentage theatre utilisation in a hospital. i have a field to caculate the actual minutes used in a theatre and my set analysis on this are. TheatreTotaltime is the actual minutes that the theatres were used. this i must used to calculate the percentage utilisation of the theatres
=
Sum({$<Year={"$(=Year(Today()))"},Month={">=01 <=$(=Num(Month(Today()))-1)"}>} TheatreTotalTime)
Louw,
Give this a try:
=
num(daynumberofyear(monthstart(today())-1)*24*60,'##,###')
We're essentially finding the start date of the current month we're in and then taking '1' from it to give us the day number of the last day of last month...and then we do the hours and minutes multiplication.
Hope that solves it.
Matt - Visual Analytics Ltd
Give this a try:
=num((DayNumberOfYear(now())-1)*24*60+(Hour(now())*60)+minute(now()),'##,###')
First section works out the minutes up until midnight yesterday, then how many minutes in the completed hours for today and finally how many minutes in the current hour.
Be warned this is using the now() function and could well lead to performance issues - I'd recommend using today() to limit the processing impact as now() needs to be recalculated every second as the time changes.
Hope that helps,
Matt - Visual Analytics Ltd
Hi Matt,
Thanks for the reply and i did test it and it seems its is going to be my solution with a slight modification. There is just one hickup, the client see YTD always as up to the last completed month. For instance if it is middle march now and we load data today then ytd will be to the end of Feb. This means that i must calculate the minutes up to end of Feb. Any idea on this.
thks so much for your help.
Louw
Louw,
Give this a try:
=
num(daynumberofyear(monthstart(today())-1)*24*60,'##,###')
We're essentially finding the start date of the current month we're in and then taking '1' from it to give us the day number of the last day of last month...and then we do the hours and minutes multiplication.
Hope that solves it.
Matt - Visual Analytics Ltd
I think it can be as simple as this:
(MonthStart(today()) - YearStart(today())) * 24 * 60
Mat thanks your suggestion it worked and is solving my YTD minutes calculation.
My client want to see utilization now for last completed month if it is the 14 march today they want to see the utilization of the last completed month's theatre usage. Now i must calculate the minutes for last month and this will be now february. daynumberofyear is not going to work here is there a function like daynumberofmonth or simular i need now to calculate the total minutes for last completed month.
Thank so much for your help
Louw
Give this a try:
=num(day(monthend(addmonths(today(),-1)))*24*60,'##,###')
Hope that helps,
Matt - Visual Analytics Ltd
Mat thank you for your help it worked 100%
Regards
Louw
Hi Mat,
You gave me the solution to calculate the number of minutes for year to date
num(daynumberofyear(monthstart(today())-1)*24*60,'##,###')
By using this i can also caculate the number of days by just taking away the * 24 and the * 60 and it worked 100% for me, but now i need to calculate the number of days in the fiscal year to date. This will now be the number of days from 1 Oct 2010 up to the last day of the last completed month that is Feb 2011.
Do you have and idea how to calculate this.
Your help is appreciated.
Regards
Louw
Should be straight forward: take the formula you have for the number of days in this calendar to date and then add 92 - 31 Days in Oct + 30 Days in Nov + 31 Days in Dec.
num(daynumberofyear(monthstart(today())-1)+92
(Un-checked syntax).
The days between 01 Oct 2010 and the start of 2011 will never change so it's simply adding 92.
Hope that helps,
Matt - Visual Analytics Ltd