Hi
I am using LibreOffice Calc Version: 4.2.4.2
My issue is that while trying to subtract two cells with Date & Time input together, I get
Fault Date & Time in NMS; OSP; Maintenance Team; Restore Date & Time in NMS; Total Downtime
7/2/14 16:49 Fazlu Alamin-2 7/2/14 21:22 30:04:33
The “Total Downtime” is formatted as (DD\hh:mm). The result should only be 4:33 Hrs:Mins
What am I doing wrong here:
Thanks
Ferdaus
I interprete your statement from a service operation point of view in the way that you enter all data
7/2/14 16:49 Fazlu Alamin-2 7/2/14
21:22 30:04:33
manually.
You have the chance to enter both date and time information into one cell like
click into the cell and type 14/07/02 16:49 enter. Note that with between …/02 and 16:4… there is a space. To see the date and time information you need to format the cell.
A possible format is yyyy-mm-dd hh:mm In this case your cell will show 2014-07-02 16:49. Note that also in the cell format there is a space between …-2014 and hh:m…
For the date format you can choose what ever suits you and the date entry could still be as indicated yy/mm/dd
Now the formula to calculate the downtime depends on how your machine downtime is defined in your service agreement. In the most simple case where the downtime clock counts 24h in a full day you just can use the following arrangement in your spreadsheet:
A2 = start date and time (in format explained about)
B2 = name of your maintenance team
C2 = end (restore) date and time (in format explained about)
D2 = Downtime based on 24h downtime per day, Saturdays, Sunday and National Holidays included.
The formula in D2 needs to be: =C2-A2 and the format of D2 needs to be hh:mm
The result should look like this:
If you want/need to enter the date and time in different cells, than calculation becomes more complex. In this case I recommend to have a look into the free manual, which you can download here: http://www.libreoffice.org/get-help/documentation/ and come back here if you still have questions.
Calculating downtimes based on normal business hours (e.g. 09:00-18:00) is possible as well it is possible to incl/exclude weekends and holidays. Formula can become pretty complex and depend on the way you need to calculate the down time.
You can not simply change the format of the cell to provide the number of days.
What ANY spreadsheet does is refer dates to a Zero time (in the case of Calc the default is 30-12-1899 00:00:00), so when you subtract two dates, you get the remainder which can be displayed as hours. However it is still a fraction (in your case 0,1895833334) of a day (24h = 1 unit) which if formatted back to Date Time would be 30-12-1899 04:33:00 and therefore the 30 that you get.
What you need to do is create a formula to get the Integer part and show it as days and the fractional part and show as hours.
Example =TRUNC(B1-A1)&"D "&TEXT((B1-A1)-TRUNC(B1-A1);“HH:MM”)
Maybe what you need is to use the format:
[HH]:MM:SS
in a cell when hours format is inside square brackets doesn’t restrict to 24 hours format, it’s possible to show e.g. 45:10:05
Thanks a lot
It used to work ok. I am having the problems very recently.
I’ll give this a try and come back