[Solved] Time & Minutes In Quarters...

[JoelP]

Hello Team,

I am looking to make the attach spreadsheet time and minutes calculate to .25 same as quarters.

For Example:

Cell Z7 Input: 08:00 AM
Cell AG7 Input: 03:15 PM
Cell AD8 Auto Input: 07:15 that is a total of 7 Hours and 15 minutes. I need it to read 7.25 meaning 7 hours and a quarter
Cell AJ8 Auto Iput: 07:15 that is a WTD total of Hour and 15 Minutes. I need it to read a 7.25 which is 7 Hours and a quarter.

I will need this same formula for the whole sheet from Thursday to Wednesday.

Your help is greatly appreciated...

Thanks,

Joel...

[FJCC]

Change the format of AD8 to be a number format and change the formula to be

Code: Select all

=IF(OR(ISTEXT(Z7);ISTEXT(AG7));0;IF(AG7-Z7<0;AG7+1;AG7)-Z7) * 24

That will give you a numeric result in units of hours. Times and dates are in units of days, so multiplying by 24 changes the result to hours.

[JoelP]

Hello Team,

When I try changing the formula it actually calculates 174 for some reason.

Are you able to add the formula direct to the cell so I can see it become the .25 for every 15 minutes of the hour.

Your help is truly appreciated...

Thanks,

Joel...

[Villeroy]

FJCC's formula pasted into any empty cell of your sheet returns 7.25

[FJCC]

See AD8

[RusselB]

The 174 is the display using the [HH]:MM cell format for the calculated number of 7.25
Change the format of the cell to General

[JoelP]

Hello Team,

Thanks to all for all your help. I was able to get it resolved.

The fix was easier than I anticipated but off course with the help of all of you. Thanks so much to all again...

Thanks,
Joel...

[RusselB]

FYI an explanation of why you were seeing 174 for an entry of 7.25
7.25 in Calc as a date represents 7.25 days, not 7.25 hours.
7 days at 24 hours each is 168 hours
.25 days at 24 hours is 6 hours
168 + 6 = 174

[Lupp]

Just a word from the backseat:

7.25 in Calc as a date represents 7.25 days, not 7.25 hours.

That's not quite correct. Dates are integers (whole numbers) in Calc. 7.25 can be interpreted as a date-time value, however. This done it stands for "1900-01-06 06:00:00" if the default setting for day zero (1899-12-30) was not changed.
Spreadsheets always mess up Time-Of-Day which gravely depends on conventions and enactments with the technical/scientific quantity time which is the physical quantity nowadays measurable with the highest precison of all quantities (rel. about 10E-17). We won't need that high precision, but we need a valid concept of duration. Spreadsheets haven't any. If we nonetheless use numeric values in spreadsheets together with the standard formatting facilities we have to accept the usage of the day as the default unit of time ignoring the fact that the day is basically unsuitable for the purpose since it hasn't a well defined duration.
Just one example from my pay-sheet, given in a way valid in Germany:
If I enter 2017-03-26 01:30 under StartOfWork and 2017-03-26 03:30 under EndOfWork for an emergency rescuer, and calculate the duration as the difference with Calc, I get 2.00 hours. In fact it was 1.00 hours. A StartOfWork of 2017-03-26 02:30 will also be accepted despite the fact that this point of time didn't exist at all due to decreed time-shift.

Hopefully the OQ never has to calculate durations spanning a time-shift. He wouldn't worry about the occasional leap-seconds, but probably about the fact that at the time-shift in the autumn there is an hour which must be passed twice. How to handle in Calc?