Hello everyone,
I'm looking for assistance with a complex calculation in Google Sheets and would appreciate any guidance or advice you can provide.
Here's my challenge:
I need to calculate the total trading minutes between two given dates and times. My trading hours are from 6:00 PM to 5:00 PM the following day. Additionally, there's a non-trading hour each day from 5:00 PM to 6:00 PM, and the weekends are non-trading periods from Friday 5:00 PM to Sunday 6:00 PM.
I have been struggling to create a single Google Sheets formula that can accurately calculate the trading minutes for various date ranges, considering these specific trading and non-trading hours.
Example:
I need to calculate the trading time for periods like from 9/14/2022 10:55:00 to 9/15/2022 9:30:00, from 4/6/2023 16:50:00 to 4/9/2023 18:00:00, and from 4/10/2023 1:30:00 to 4/10/2023 8:15:00.
The main challenges are:
Handling the specific start and end times within the trading and non-trading hours.
Accurately accounting for weekends and the non-trading hour each day.
Creating a dynamic formula that adapts to different date ranges.
I understand that this is a complex scenario that might be challenging to solve with a standard Google Sheets formula. I'm open to suggestions, whether it involves advanced formulas, Google Apps Script, or any other approach that could work.
Thank you in advance for your time and help!
I'm looking for assistance with a complex calculation in Google Sheets and would appreciate any guidance or advice you can provide.
Here's my challenge:
I need to calculate the total trading minutes between two given dates and times. My trading hours are from 6:00 PM to 5:00 PM the following day. Additionally, there's a non-trading hour each day from 5:00 PM to 6:00 PM, and the weekends are non-trading periods from Friday 5:00 PM to Sunday 6:00 PM.
I have been struggling to create a single Google Sheets formula that can accurately calculate the trading minutes for various date ranges, considering these specific trading and non-trading hours.
Example:
I need to calculate the trading time for periods like from 9/14/2022 10:55:00 to 9/15/2022 9:30:00, from 4/6/2023 16:50:00 to 4/9/2023 18:00:00, and from 4/10/2023 1:30:00 to 4/10/2023 8:15:00.
The main challenges are:
Handling the specific start and end times within the trading and non-trading hours.
Accurately accounting for weekends and the non-trading hour each day.
Creating a dynamic formula that adapts to different date ranges.
I understand that this is a complex scenario that might be challenging to solve with a standard Google Sheets formula. I'm open to suggestions, whether it involves advanced formulas, Google Apps Script, or any other approach that could work.
Thank you in advance for your time and help!
