PDA

View Full Version : A day longer than 24 hours



bluescan
05-20-2016, 11:31 PM
Good morning forum!

I am currently working on a day planner for my company. Tasks to be completed at specific times during the day are assigned to teams. This is then drawn graphically on a timeline for the day. So far it has been working great, but so far the day in my sheet has started at 00:00 and ended at 23:59. Now I need to be able to overlap between days. Example: The first shift starts 04:00 and the last shift ends at 03:00 the next day. Therefore, the plan must start at 04:00 and end 03:00 the next day.

Now I run into problems with Excel and dates. In my previous versions, all time formats have been formatted as "hh:mm". My first try has been to write f.ex. 03:00 the next day as 27:00, and using "[hh]:mm" as formatting but it turned out not to be that simple. On the sheet 27:00 is shown, but when I select the cell the value actually is "1/1/1900 03:00". If I select 23:00 on the sheet, it's value is just "23:00". They have all been formatted the same way, with "[hh]:mm". It also seems like VBA actually picks up "27:00" as "31/12/1899 03:00" when I see values during debug ( I guess this is the basic start date + 27 hours).

This issue has caused a lot of frustration and I find my self a bit stuck.
Do anybody have a good suggestion on how to handle "things after mid-night" in a simple matter?

Best regards,
Bluescan

SamT
05-21-2016, 07:53 AM
DateTimes are stored in Excel as Doubles, (Decimal numbers.) Dates are whole numbers, and are calculated by counting the number of days since 1/1/1900. Enter 0 into a date cell.

Times are fractional parts of a day. 0.25 = 6AM, 0.5 = 12N. There are 85400 seconds in a day, so one second is stored in Excel as the value of 1/86400.


anybody have a good suggestion on how to handle "things after mid-night" in a simple matter?Not me.

How it is handled truly depends on exactly what you are doing on the Worksheet. If you use the Go Advanced button, below the advanced editor, you will see a Manage Attachments button. That will let you upload a simplified version of your workbook so we can see how to handle your exact problem.

A simple scenario is when a line of times includes the date value, (formatted as times (h:mm)) Then you can just add the correct Hour decimal to the previous datetime. 8 hours = (60s*60m*8h)/86400. (Clearing zeros: 6x6x8/864)

A1 MMMMMMMMMMM B1
05/21/2016 8PMMMM =A1+.29166667


If I was going this route, I would just add the shift length in hours/24: =A1+8/24; =B1+9/24; =C1+7/24