What time is it right now (e.g. 2:15PM) & how do I calculate off that?

[Ersh]

I'm working on a scheduling tool and I need to run a variety of time calculations. It's hard to explain all the nitty gritty details, but at a high level I'm doing a lot of back and forth conversion between decimals and time. I'm wondering what your thoughts are...do you try to do your math directly on Time fields or is it just as simple to use decimals?

	A	B	C	D
1	Label	Data	Data Format	Formula
2	current time (as fraction of 24 hours) <tbody> </tbody>	0.6270 <tbody> </tbody>	Number <tbody> </tbody>	=NOW()-TODAY() <tbody> </tbody>
3	current time (hours) <tbody> </tbody>	15.0474 <tbody> </tbody>	Number <tbody> </tbody>	=B2*24 <tbody> </tbody>
4	current time (whole hours) <tbody> </tbody>	15 <tbody> </tbody>	Number <tbody> </tbody>	=LEFT(B3,FIND(".",B3)-1) <tbody> </tbody>
5	current time (minutes) <tbody> </tbody>	2.8457 <tbody> </tbody>	Number <tbody> </tbody>	=MID(B3,FIND(".",B3),10)*60 <tbody> </tbody>
6	current time (whole minutes) <tbody> </tbody>	2 <tbody> </tbody>	Number <tbody> </tbody>	=LEFT(B5,FIND(".",B5)-1) <tbody> </tbody>
7	current time (seconds) <tbody> </tbody>	50.7400 <tbody> </tbody>	Number <tbody> </tbody>	=MID(B5,FIND(".",B5),10)*60 <tbody> </tbody>
8	current time (whole seconds) <tbody> </tbody>	50 <tbody> </tbody>	Number <tbody> </tbody>	=LEFT(B7,FIND(".",B7)-1) <tbody> </tbody>
9	current time (re-constructed) <tbody> </tbody>	3:02:50 PM <tbody> </tbody>	Time <tbody> </tbody>	=TIME(B4,B6,B8) <tbody> </tbody>
10	end of day (4:45 PM) <tbody> </tbody>	4:45 PM <tbody> </tbody>	Time <tbody> </tbody>	4:45 PM <tbody> </tbody>
11	end of day (4:45 PM as fraction of 24 hours) <tbody> </tbody>	0.6979 <tbody> </tbody>	Number <tbody> </tbody>	=(16+0.75)/24 <tbody> </tbody>
12	remaining time (hours) <tbody> </tbody>	1.7026 <tbody> </tbody>	Number <tbody> </tbody>	=(B11-B2)*24 <tbody> </tbody>
13	remaining time (minutes) <tbody> </tbody>	42.1543 <tbody> </tbody>	Number <tbody> </tbody>	=MID(B12,FIND(".",B12),10)*60 <tbody> </tbody>
14	remaining time (seconds) <tbody> </tbody>	9.2600 <tbody> </tbody>	Number <tbody> </tbody>	=MID(B13,FIND(".",B13),10)*60 <tbody> </tbody>

<tbody>
</tbody>

 

[RickXL]

Hi.

I would always use the built-in date and time functions. Internally they expect the dates to be in whole numbers of days after1/1/1900 and the hours are stored as fractions of days so they make up the fractional part of the number.

If you do that you will have all these functions you can use plus easy use of the built-in date and time formats: https://support.office.com/en-gb/ar...4e4-a2bc-3230a10786fa&ui=en-US&rs=en-GB&ad=GB

The functions will let you add days, subtract dates, calculate working days between two dates, find the end of a month etc etc.