Another Excel question: apply a date to a time value without altering time?

[destrekor]

So as I've been creating a schedule (it's finished! It's beautiful!), I was filling in times for testing but that was it. I discovered when entering times that go beyond 12am the next day, the calculations and graphing go out of whack.

Now I corrected for this by setting the time, say, 1am, as 25:00, and Excel automagically corrected that to 1 AM... on 1/1/1990. The other times don't have any date applied. But apparently no date = the day before 1/1/1990 as far as calculations are concerned.

Now, that's not so bad, but if I ever hand off this schedule and no longer manage it, then I cannot trust that things will work perfectly without them stumbling into a few "bugs" and having to work out what the hell is happening.

I put an instruction note at the top of the main data sheet from which all the other sheets pull specific data, but I'd like to nip this in the bud and get it working in a more fluid and accurate manner instead of it functioning correctly due to the wrong reasons.


So if I have a table filled with times in many cells, but without a date actually attached, how would I apply dates that are in the future (schedule starts 2/28/2016) to the existing cells?

I did that just now without any formula tricks, but when I set the cell formatting to show only the time, all the cells now show 12:00 AM. In order to help ensure everything works right, I'd much rather cells be empty unless that person is scheduled. Everything corrects elsewhere for empty cells or bad calculations due to that, so that much works smoothly. It's also a hell of a lot easier to read when editing the full schedule.

I'd like to not have to enter the full date when the times are being entered into the spreadsheet.

 

[Tweak155]

You want times without dates attached, but want the times on specific dates... that doesn't make any sense.

You should be storing the date and time together and changing the format of what is displayed to time only. Sounds like your first attempt only displayed 12am, however you can definitely do this.

 

[destrekor]

You want times without dates attached, but want the times on specific dates... that doesn't make any sense.

You should be storing the date and time together and changing the format of what is displayed to time only. Sounds like your first attempt only displayed 12am, however you can definitely do this.

It sounds like if you put a date in there, it has a time serial just as it would if it were a specific hh:mm value. So if you just set a date and format to display the hours, it will display 12:00am. There appears to be no way around that.

Considering I do have an intermediary sheet that pulls only the selected week, from which another sheet then does it's work (I could have done this more efficiently as I have since learned, but now I am seeing I am very glad I created this), I believe I can apply a sum between a column with the dates for each row and the time entered, if the VLOOKUP statement within an if statement does find a value has been entered in that specific cell.

It helps greatly to know how Excel stores time. I had no idea it turned every date and time into a common serial value. Actual time is just calculated as a decimal value for any given day.

 

[cbrunny]

yeah date-time is one continuous value. you can convert it to a decimal'd number. e.g. feb 22 2016 @ 1:00 pm = 42402.541666

feb 22 2016 @ 1:01 pm = 42402.542361

feb 22 2016 @ 1:01:01 pm = 42402.542372685

It's still weird, because it converts time to base 10. So each second is not worth .000001 or whatever. it's worth 0.000011574074, and each minute is worth 0.000694, each hour worth 0.0416, and each day worth 1.0.



but to answer your question, I agree with the other poster. I'd force excel to calculate everything correctly and adjust the output formatting to suit.

 

[destrekor]

yeah date-time is one continuous value. you can convert it to a decimal'd number. e.g. feb 22 2016 @ 1:00 pm = 42402.541666

feb 22 2016 @ 1:01 pm = 42402.542361

feb 22 2016 @ 1:01:01 pm = 42402.542372685

It's still weird, because it converts time to base 10. So each second is not worth .000001 or whatever. it's worth 0.000011574074, and each minute is worth 0.000694, each hour worth 0.0416, and each day worth 1.0.



but to answer your question, I agree with the other poster. I'd force excel to calculate everything correctly and adjust the output formatting to suit.

Well putting the whole thing is also appears to screw with charting the times.

So only the time is entered on one sheet, the next sheet pulls all of that time and sums the specified date with the cell value it is pulling, and formatting still keeps it showing only the time. Then in the next sheet where all the chart math is happening, in the formula where I am pulling the data (and also changing the value based on time zone selection), I am then subtracting the pure date value, which in turn leaves only the same raw time as entered in the original sheet. Which, truth be told, kind of makes the whole date adding step quite pointless, but I didn't want the dates to show up as January 1, 1990, which happens if you enter 24:00 or higher in the cell, it assigns it as the next day from 0.0, where 1/1/1990, 00:00 is 1.0. If you only enter 12 am or 1 am, Excel assumes you mean 0.####, which was screwing with my charts. But the intermediary step just makes the time accurate to the correct date - mostly for peace of mind, but in the end it is accomplishing absolutely nothing. lol

It's there, at least, in case we want to do any detailed reports, though I highly doubt it.

 

[cbrunny]

You should just change the labels over from Date and Time to "Stardate" - Roddenberry basically invented this already. lol.