Trying to SUM working hours after midnight
I have this formula:
=ROUNDDOWN(((B1-A1)*24) ; 1)
which calculates the duration between two times (B1=end time, A1=start time) and then converts it to decimal.
The current problem I have with this formula is that it is unable to give a correct answer when the end time is past midnight.
How can I improve the formula to make it work when the end time is past midnight?
Solution
Please try the following for a list of dates
=INDEX(ROUNDDOWN(((B2:B6-A2:A6+(B2:B6<A2:A6))*24) ; 1))
(Do adjust the formula according to your ranges and locale)
- What is the Z ending on date strings like 2014-01-01T00:00:00.588Z
- Batch file to delete files older than N days
- Java: display time at user entered GMT offset
- How can I compare two Calendar dates?
- Strange behaviour of ww SimpleDateFormat
- `uuuu` versus `yyyy` in `DateTimeFormatter` formatting pattern codes?
- Difference between CURRENT_TIMESTAMP and CURRENT_DATE
- Vectorise find closest date function
- Converting UTC date format to local nsdate
- Is it possible to condition CanCan abilities on a date attribute range?
- Convert "dd-mon-year" to date in R
- Start of week for locale
- How to get the first day of the current week and month?
- Sort a 2d array by a column of d-n-Y formatted dates
- Get first day of a particular week
- Carbon: Get start and end date of week when knowing week in year and year
- Create table that splits records on specific dates using SAS
- how to convert date 2017-sep-12 To 2017-09-12 in HIVE
- getting compile-time date and time without macros
- Sqllite query convert timestamp to date
- Automating Date Headers for printable monthly attendance sheets
- How to check Daylight Saving Time with Temporal in Javascript?
- Converting java.util.Date (01.01.0001) to java.time.LocalDate returns 29.12.0000
- Occurrence by month using Excel Functions
- Calendar failed to get the days names in different languages than English
- How to show day name in SQL Server?
- How can I use PHP to list all the dates in a year?
- ORA-01861 Error When Trying to Create WHERE Date is 6 Months From Current Date
- Java Date/Calendar oddness
- Java Date Time discrepancy